package com.cfs.daoimpl.sys;

/**
 * 数据库操作语句定义
 * 
 * @author SDZJ
 *
 */
public class DB_SQL { 

	// 为便于定位查找
	// 语句常量按：表名_操作_方式_其它___ 定义，
	// 并以常量名字母顺序排列、以表名分组
	
	//获取系统时间
	public static final String SYSTIME = "select to_char(sysdate,'yyyy-mm-dd-hh24-mi') from dual";
	public static final String SYSTIME_MM = "select to_char(sysdate,'yyyy-mm-dd-hh24-mi-ss') from dual";
	
	// 级联字典
	public static final String CASC_DEL_BY_ID = "DELETE FROM TB_CASC WHERE ID=?"; 
	public static final String CASC_DEL_BY_NAME = "DELETE FROM TB_CASC WHERE NAME=?";
	public static final String CASC_DEL_BY_NAME_PK = "DELETE FROM TB_CASC WHERE NAME=? AND PK=?";
	public static final String CASC_DEL_BY_NAME_PK_KEY = "DELETE FROM TB_CASC WHERE NAME=? AND PK=? AND EKEY=?";
	public static final String CASC_INS = "INSERT INTO TB_CASC(ID, PK, NAME, EKEY, EVALUE, MEMO) VALUES(SEQ_CASC.NEXTVAL,?,?,?,?,?)";
	public static final String CASC_UPD_BY_ID = "UPDATE TB_CASC SET PK=?, NAME=?, EKEY=?, EVALUE=?, MEMO=? WHERE ID=?";
	public static final String CASC_QUERY_ALL = "SELECT * FROM TB_CASC ORDER BY ID";
	public static final String CASC_QUERY_ALL_NAMES = "SELECT DISTINCT NAME FROM TB_CASC";
	public static final String CASC_QUERY_BY_NAME = "SELECT * FROM TB_CASC WHERE NAME=? AND PK IS NULL ORDER BY ID";
	public static final String CASC_QUERY_BY_NAME_PK = "SELECT * FROM TB_CASC WHERE NAME=? AND PK=? ORDER BY ID";

	// 级联字典
	public static final String DICT_DEL_BY_ID = "DELETE FROM TB_DICT WHERE ID=?";
	public static final String DICT_DEL_BY_NAME = "DELETE FROM TB_DICT WHERE NAME=?";
	public static final String DICT_DEL_BY_NAME_KEY = "DELETE FROM TB_DICT WHERE NAME=? AND EKEY=?";
	public static final String DICT_INS = "INSERT INTO TB_DICT(ID, NAME, EKEY, EVALUE, MEMO) VALUES(SEQ_DICT.NEXTVAL,?,?,?,?)";
	public static final String DICT_UPD_BY_ID = "UPDATE TB_DICT SET NAME=?, EKEY=?, EVALUE=? WHERE ID=?";
	public static final String DICT_QUERY_ALL = "SELECT * FROM TB_DICT ORDER BY ID";
	public static final String DICT_QUERY_ALL_NAMES = "SELECT DISTINCT NAME,MEMO FROM TB_DICT ORDER BY NAME";
	public static final String DICT_QUERY_ID_BY_NAME = "SELECT ID FROM TB_DICT WHERE NAME=? ORDER BY ID";
	public static final String DICT_QUERY_BY_NAME = "SELECT * FROM TB_DICT WHERE NAME=?";

	// 用户菜单
	public static final String MENU_DEL_BY_ID = "DELETE FROM TB_MENUS WHERE ID=?";
	public static final String MENU_DEL_BY_PID = "DELETE FROM TB_MENUS WHERE PID=?";
	public static final String MENU_INS = "INSERT INTO TB_MENUS(ID, PID, NAME, PAGE) VALUES(?, ?, ?, ?)";
	public static final String MENU_QRY_ALL = "SELECT * FROM TB_MENUS ORDER BY ID";
	public static final String MENU_QRY_BY_ID = "SELECT * FROM TB_MENUS WHERE ID=?";
	public static final String MENU_QRY_BY_NAME = "SELECT * FROM TB_MENUS WHERE NAME=?";
	public static final String MENU_QRY_BY_PID = "SELECT * FROM TB_MENUS WHERE PID=?";
	public static final String MENU_UPDATE = "UPDATE TB_MENUS SET PID=?, NAME=?, PAGE=? WHERE ID=?";

	public static final String MENU_RIGHT_DEL_BY_KEY = "DELETE FROM TB_MENU_RIGHT WHERE MENUID=? AND RIGHTID=?";
	public static final String MENU_RIGHT_INS = "INSERT INTO TB_MENU_RIGHT(MENUID, RIGHTID) VALUES(?, ?)";
	public static final String MENU_RIGHT_QRY_BY_MENUID = "SELECT * FROM TB_MENU_RIGHT MR LEFT JOIN TB_RIGHT R ON MR.RIGHTID=R.ID WHERE MR.MENUID=? ORDER BY MR.RIGHTID ASC";
	public static final String MENU_RIGHT_QRY_ALL = "SELECT * FROM TB_RIGHTS ORDER BY ID ASC";

	public static final String MENU_ROLE_DEL_BY_KEY = "DELETE FROM TB_MENU_ROLE WHERE ROLEID=? AND MENUID=?";
	public static final String MENU_ROLE_DEL_BY_ROLE = "DELETE FROM TB_MENU_ROLE WHERE ROLEID=?";
	public static final String MENU_ROLE_DEL_BY_MENU = "DELETE FROM TB_MENU_ROLE WHERE MENUID=?";
	public static final String MENU_ROLE_INS = "INSERT INTO TB_MENU_ROLE(ROLEID, MENUID) VALUES(?,?)";
	public static final String MENU_ROLE_QRY_BY_ROLEID = "SELECT * FROM TB_MENUS M LEFT JOIN TB_MENU_ROLE R ON R.MENUID=M.ID WHERE R.ROLEID=? ORDER BY M.ID";
	
	//组织机构表
	public static final String ORG_DEL_BY_ID = "DELETE FROM TB_ORG WHERE ID=?";
	public static final String ORG_INS = "INSERT INTO TB_ORG(ID,PARENTID,ORGCODE,LEVELS,STATE,NAME,ADDRESS,DESCRIPTION) VALUES(SEQ_ORG.nextval,?,?,?,?,?,?,?)";
	public static final String ORG_UPD_BY_ID = "UPDATE TB_ORG SET PARENTID=?,ORGCODE=?,LEVELS=?,NAME=?,ADDRESS=?,DESCRIPTION=? WHERE ID=?";
	public static final String ORG_QUERY_BY_ID = "SELECT * FROM TB_ORG WHERE ID=?";
	public static final String ORG_QUERY_BY_PARENTID = "SELECT * FROM TB_ORG WHERE PARENTID=?";
	public static final String ORG_QUERY_ALL = "SELECT ID, PARENTID, LEVELS, STATE, NAME, ADDRESS, DESCRIPTION, ORGCODE FROM TB_ORG WHERE STATE = 1 ORDER BY LEVELS ASC";
	public static final String ORG_MOVE2 = "UPDATE TB_ORG SET PARENTID=? WHERE ID=?";
	public static final String ORG_UPDATE_STATE="UPDATE TB_ORG SET STATE=? WHERE ID=?";
	public static final String ORG_QUERY_STATE_BY_ID="SELECT STATE FROM TB_ORG WHERE ID=?";
	public static final String ORG_QUERY_PARENT_BY_ID="SELECT * FROM TB_ORG WHERE ID IN(SELECT PARENTID FROM TB_ORG WHERE ID=?)";
	public static final String ORG_QUERY_PARENT1_BY_ID="SELECT * FROM TB_ORG WHERE ID IN(SELECT PARENTID FROM TB_ORG WHERE ID IN(SELECT PARENTID FROM TB_ORG WHERE ID=?))";
	public static final String ORG_QUERY_PARENT2_BY_ID="SELECT * FROM TB_ORG WHERE ID IN(SELECT FILIALE FROM TB_POINT WHERE ID=?)";
	public static final String ORG_QUERY_LEVELS_BY_ID="SELECT LEVELS FROM TB_ORG WHERE ID=?";
	public static final String ORG_QUERY_ZONE2="SELECT ID FROM TB_ORG WHERE PARENTID=?";
	public static final String ORG_QUERY_BY_NAME = "SELECT * FROM TB_ORG WHERE NAME ";
	public static final String ORG_QUERY_AGENCY = "SELECT ID,NAME,LEVELS,ORGCODE FROM TB_ORG WHERE PARENTID = ?";
	public static final String ORG_QUERY_AREA = "SELECT ID,NAME,LEVELS,ORGCODE FROM TB_ORG";
	/**
	 * 网点表
	 */
	public static final String POINT_DEL_BY_ID = "DELETE FROM TB_POINT WHERE ID=?";
	public static final String ORG_UPD_BY_ID_POINT = "UPDATE TB_ORG SET NAME = ?,STATE=? WHERE ID=?";
	public static final String POINT_LOG_INSERT="INSERT INTO TB_POIN_STA_MONI(ID,OPTNAME,PTNAME,OPTDATE,OPT) VALUES(SEQ_PMONI.nextval,?,?,?,?)";
	public static final String POINT_INS = "INSERT ALL INTO TB_ORG(ID, NAME, PARENTID, LEVELS, STATE, ORGCODE) VALUES (SEQ_ORG.nextval, ?, ?, 5, 1, ?) INTO TB_POINT(ID, FILIALE, AGENCY, AREA, SHENG, SHI, XIAN, MKLEVEL, SHORTNAME, FULLNAME, ADDRESS, POSTCODE, PROMONUM, OPENTIME, REGITIME, CREATETIME, STATE, MONOPOLY, TRADEAREA, SPACEFEE, STANDRANK, STANDAREA, PIPETYPE1, PIPETYPE2, PKIND1, POINTTYPE1, SUPPLYPOINT1, PKIND2, POINTTYPE2, SUPPLYPOINT2, PKIND3, POINTTYPE3, SUPPLYPOINT3, PKIND4, POINTTYPE4, SUPPLYPOINT4,SAPCODE,ISBILLING,BUSINESSTYPE) VALUES (SEQ_ORG.currval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) SELECT 2 FROM DUAL";
	public static final String POINT_INS_BY_AREA = "SELECT TG.ID AS AREA, TG1.ID AS AGENCY, TG2.ID AS FILIALE, TG3.ID AS ZONE2, TG3.PARENTID AS ZONE1 FROM(SELECT ID, PARENTID FROM TB_ORG WHERE ID = ?) TG LEFT JOIN (SELECT ID, PARENTID FROM TB_ORG) TG1 ON TG1.ID = TG.PARENTID LEFT JOIN (SELECT ID, PARENTID FROM TB_ORG) TG2 ON TG2.ID = TG1.PARENTID LEFT JOIN (SELECT ID, PARENTID FROM TB_ORG) TG3 ON TG3.ID = TG2.PARENTID";
	public static final String POINT_UPD_BY_ID = "UPDATE TB_POINT SET SHENG=?,SHI=?,XIAN=?,MKLEVEL=?,SHORTNAME=?,FULLNAME=?,ADDRESS=?,POSTCODE=?,PROMONUM=?,OPENTIME=?,REGITIME=?,CREATETIME=?,CLOSETIME=?,STATE=?,MONOPOLY=?,TRADEAREA=?,SPACEFEE=?,STANDRANK=?,STANDAREA=?,PIPETYPE1=?,PIPETYPE2=?,COMPETLOG=?,PKIND1=?,POINTTYPE1=?,SUPPLYPOINT1=?,PKIND2=?,POINTTYPE2=?,SUPPLYPOINT2=?,PKIND3=?,POINTTYPE3=?,SUPPLYPOINT3=?,PKIND4=?,POINTTYPE4=?,SUPPLYPOINT4=?,userdef1=?,userdef2=?,sapcode=?,isbilling=?,BUSINESSTYPE=? WHERE ID=?";
	public static final String POINT_QUERY_BY_ID = "SELECT * FROM TB_POINT WHERE ID=?";
	public static final String POINT_IMP = "INSERT INTO TB_POINT(ID,FILIALE,AREA,AGENCY,SHENG,SHI,XIAN,MKLEVEL,STATE,"
		+ "SHORTNAME, FULLNAME,ADDRESS,POSTCODE, PKIND1, POINTTYPE1, SUPPLYPOINT1, PKIND2, POINTTYPE2, SUPPLYPOINT2, PKIND3, POINTTYPE3, SUPPLYPOINT3, PKIND4, POINTTYPE4, SUPPLYPOINT4,"
		+ "mgrname,mgrphone,mgrmobile) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String TEST_IMP = "INSERT INTO TB_TEST(ID, FILIALE, ZONE2, MKLEVEL, AGENCY, AREA, ZONE1, SHENG, SHI, XIAN, "
		+ "SHORTNAME, FULLNAME, EXTEND1, ADDRESS, MONOPOLY, POSTCODE, MANAGER, SALESMAN, PROMONUM, OPENTIME, "
		+ "TRADEAREA, STANDRANK, SPACEFEE, STANDAREA, POINTTYPE, SUPPLIER, PIPETYPE1, PIPETYPE2, STATE, REGITIME, CREATETIME, CLOSETIME, COMPETLOG,mgrname,mgrphone,mgrmobile,salename,salephone,salemobile) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String POINT_QUERY_BY_AREA="SELECT * FROM TB_POINT WHERE AREA=?";
	public static final String POINT_UPDATE4= "UPDATE TB_POINT SET FILIALE=?,AGENCY=?,AREA=? WHERE ID=?";
	public static final String POINT_QUERY_IDS3="SELECT ID FROM TB_POINT WHERE AREA=?";
	public static final String POINT_UPDATE3= "UPDATE TB_POINT SET FILIALE=?,AGENCY=? WHERE ID=?";
	public static final String POINT_QUERY_IDS2="SELECT ID FROM TB_POINT WHERE AGENCY=?";
	public static final String POINT_UPDATE2 = "UPDATE TB_POINT SET FILIALE=? WHERE ID=?";
	/**
	 * 终端门店表
	 */
	public static final String TERMINAL_DEL_BY_ID = "DELETE FROM TB_TERMINAL WHERE ID = ?";
	public static final String TERMINAL_INS = "INSERT INTO TB_TERMINAL(ID,TERMINAL,POINTID,TYPE,MEMO,SALEMAN,SIID) VALUES(SEQ_TERMINAL.nextval,?,?,?,?,?,?)";
	public static final String TERMINAL_UPD_BY_ID = "UPDATE TB_TERMINAL POINTID=?,SALEMAN=? WHERE ID=?";
	public static final String TERMINAL_QUERY_BY_ID = "SELECT * FROM TB_TERMINAL WHERE ID=?";
	public static final String TERMINAL_QUERY_BY_POINTID = "SELECT TERMINAL FROM TB_TERMINAL WHERE POINTID = ?";
	
	/**
	 * 产品表
	 */
	public static final String PRODUCT_DEL_BY_ID = "DELETE FROM TB_PRODUCT WHERE ID=?";
	public static final String PRODUCT_INS = "INSERT INTO TB_PRODUCT(ID,CODE,ISTV,CLASS1,CLASS2,CLASS3,CLASS4,TYPE1,TYPE2,TYPE3,TYPE4,SPEC,ISMAIN,ISCOUNTRY,POPUTYPE,COSTPRICE,SUPPPRICE,PRICEH,PRICEL,MKTDATE,REGIDATE,BASEPLATE,RACK,ATTACH) VALUES(SEQ_PRODUCT.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//	public static final String PRODUCT_COSTPRICE_INS = "INSERT INTO TB_PRODUCT_PRICE1(ID, CODE, COST, RECORDTIME) VALUES (SEQ_PRODUCT_PRICE.nextval,?,?,?)";
	public static final String PRODUCT_SUPPRICE_INS = "INSERT INTO TB_PRODUCT_PRICE2(ID, CODE, SUPPPRICE, RECORDTIME) VALUES (SEQ_PRODUCT_PRICE.nextval,?,?,?)";
	public static final String PRODUCT_UPD_BY_ID = "UPDATE TB_PRODUCT SET CODE=?,ISTV=?,CLASS1=?,CLASS2=?,CLASS3=?,CLASS4=?,TYPE1=?,TYPE2=?,TYPE3=?,TYPE4=?,SPEC=?,ISMAIN=?,ISCOUNTRY=?,POPUTYPE=?,COSTPRICE=?,SUPPPRICE=?,PRICEH=?,PRICEL=?,MKTDATE=?,REGIDATE=?,BASEPLATE=?,RACK=?,ATTACH=? WHERE ID=?";
	public static final String PRODUCT_QUERY_BY_ID = "SELECT * FROM TB_PRODUCT WHERE ID=?";
	public static final String PRODUCT_QUERY_CODE = "SELECT ID, CODE, PRICEH, PRICEL, COSTPRICE, SUPPPRICE FROM TB_PRODUCT";
	public static final String PRODUCT_QUERY_CODE_ISTV = "SELECT ID, CODE FROM TB_PRODUCT WHERE ISTV = ?";
	public static final String PRODUCT_QUERY_ALL = "SELECT * FROM TB_PRODUCT";
	public static final String PRODUCT_QUERY_CODE_BY="SELECT ID,CODE FROM TB_PRODUCT";
	/**
	 * 权限表
	 */
	public static final String RIGHT_DEL_BY_ID = "DELETE FROM TB_RIGHT WHERE ID=?";
	public static final String RIGHT_INS = "INSERT INTO TB_RIGHT(ID,NAME,URI) VALUES(SEQ_RIGHT.NEXTVAL,?,?)";
	public static final String RIGHT_UPD_BY_ID = "UPDATE TB_RIGHT SET NAME=?,URI=? WHERE ID=?";
	public static final String RIGHT_QUERY_ALL = "SELECT * FROM TB_RIGHT ORDER BY ID";
	public static final String RIGHT_QUERY_BY_ID = "SELECT * FROM TB_RIGHT WHERE ID=?";
	public static final String RIGHT_QUERY_BY_ROLE = "SELECT b.* FROM TB_ROLE_RIGHT a LEFT JOIN TB_RIGHT b ON a.RIGHT=b.ID WHERE a.ROLE=?";

	/**
	 * 角色表
	 */
	public static final String ROLE_DEL_BY_ID = "DELETE FROM TB_ROLE WHERE ID=?";
	public static final String ROLE_INS = "INSERT INTO TB_ROLE(ID,NAME,DESCRIPTION) VALUES(SEQ_ROLE.nextval,?,?)";
	public static final String ROLE_QUERY_ALL = "SELECT * FROM TB_ROLE ORDER BY ID";
	public static final String ROLE_QUERY_BY_ID = "SELECT * FROM TB_ROLE WHERE ID=?";
	public static final String ROLE_QUERYS = "SELECT ID, NAME FROM TB_ROLE";
	public static final String ROLE_UPD_BY_ID = "UPDATE TB_ROLE SET NAME=?,DESCRIPTION=? WHERE ID=?";
	
	/**
	 * 角色权限表
	 */
	public static final String ROLE_RIGHT_ADD = "INSERT INTO TB_ROLE_RIGHT (ROLE,RIGHT) VALUES(?, ?)";
	public static final String ROLE_RIGHT_DEL = "DELETE FROM TB_ROLE_RIGHT WHERE ROLE=? AND RIGHT=?";
	public static final String ROLE_RIGHT_QUERY_BY_ROLE = "SELECT * FROM TB_ROLE_RIGHT WHERE ROLE=?";
	public static final String ROLE_RIGHT_QUERY_URI_BY_ROLE = "SELECT b.URI FROM TB_ROLE_RIGHT a LEFT JOIN TB_RIGHT b ON a.RIGHT=b.ID WHERE a.ROLE=?";
	public static final String ROLE_RIGHT_UPD_RIGHT = "UPDATE TB_ROLE_RIGHT SET RIGHT=? WHERE ROLE=? AND RIGHT=?";

	/**
	 * 展区
	 */
	public static final String SHOWAREA_QUERY_BY_POINTID = "SELECT TS.ID AS ID, TS.POINTID AS POINTID, TS.STANDTYPE AS STANDTYPE, TD.EVALUE AS EVALUE,TS.PHOTO AS PHOTO FROM(SELECT ID, STANDTYPE, POINTID,PHOTO FROM TB_SHOWAREA WHERE POINTID=?) TS JOIN (SELECT EKEY, EVALUE FROM TB_DICT WHERE NAME =? ) TD ON TS.STANDTYPE = TD.EKEY";
	public static final String SHOWAREA_INS = "INSERT INTO TB_SHOWAREA(ID,STANDTYPE,POINTID,PHOTO,DESCRIPTION) VALUES(SEQ_SHOWAREA.nextval,?,?,?,?)";
	public static final String SHOWAREA_UPDATE = "UPDATE TB_SHOWAREA SET STANDTYPE=?, PHOTO=?, DESCRIPTION = ? WHERE ID = ?";
	public static final String SHOWAREA_DELETE = "DELETE FROM TB_SHOWAREA WHERE ID = ?";
	/**
	 * 销售记录表
	 */
	public static final String SALESREC_DEL_BY_ID = "DELETE FROM TB_SALESREC WHERE ID=?";
	public static final String SALESREC_INS = "INSERT INTO TB_SALESREC(ID,PRODID,BRAND,CATEGORY,POINTID,ORGCODE,CODE,SOURCE,STANDTYPE,PROMOTOR,TERMINAL,IDENTIFY,SALESNUM,SALESTYPE,UNITPRICE,REPORTTIME,SALESDATE,MEMO,COSTPRICE,SUPPLYPRICE,NIAN,YUE,OPERATE ,SAP ) VALUES(SEQ_SALESREC.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String SALESREC_IMPORT = "INSERT INTO TB_SALESREC VALUES(SEQ_SALESREC.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String SALESREC_UPD_BY_ID = "UPDATE TB_SALESREC SET SALESTYPE=?,OPERATE=?,MEMO=?,SALESNUM=?,UNITPRICE=? WHERE ID=?";
	public static final String SALESREC_QUERY_BY_ID = "SELECT * FROM TB_SALESREC WHERE ID=?";
	public static final String SALESREC_QUERY_BY_TERMINAL="SELECT * FROM TB_SALESREC WHERE TERMINAL=?";
	//日销量、月销量查询
	public static final String SALESREC_QUERY_VOLUME = "SELECT SALESTYPE,SUM(SALESNUM),SUM(SALESNUM * UNITPRICE) FROM TB_SALESREC WHERE REPORTTIME>=? AND REPORTTIME<? AND PROMOTOR = ? GROUP BY SALESTYPE";
	public static final String SALESREC_QUERY_BY_POINT= "SELECT SUM(SALESNUM), SUM(DECODE(SALESTYPE,1,SALESNUM)),SUM(DECODE(SALESTYPE,2,SALESNUM)),SUM(SALESNUM*UNITPRICE),SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)),SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) FROM TB_SALESREC WHERE REPORTTIME>=? AND REPORTTIME<? AND POINTID = ? ";
	public static final String SALESREC_QUERY_STORE = "SELECT SUM(SALESNUM), SUM(SALESNUM * UNITPRICE) FROM TB_SALESREC WHERE POINTID=? AND REPORTTIME>=? AND REPORTTIME<?";
	public static final String SALESREC_QUERY_STORE_1 = "SELECT MAX(1) AS BRAND,SUM(SALESNUM) AS NUM, SUM(SALESNUM * UNITPRICE) AS AMT FROM TB_SALESREC WHERE POINTID=? AND REPORTTIME>=? AND REPORTTIME<?";
	//获取组织的id
	public static final String SALESREC_IMP_FILIALE = "SELECT DISTINCT(ID) FROM TB_ORG WHERE NAME = ? AND LEVELS = 2 ";
	//查询分公司下门店的id和简称
	public static final String SALESREC_IMP_ORG = "SELECT ID, SHORTNAME FROM TB_POINT WHERE FILIALE = ?";
	//导入
	public static final String SALESREC_IMP_INS = "INSERT INTO TB_SALESREC(ID, POINTID, PROMOTOR, TERMINAL, CODE, PRODID, SALESNUM, UNITPRICE, SALESTYPE, MEMO, REPORTTIME, SOURCE, ORGCODE, SALESDATE,COSTPRICE,SUPPLYPRICE,NIAN,YUE) VALUES (SEQ_SALESREC.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	//日月销量明细查询
	public static final String SALESREC_QUERY_DETAIL = "SELECT ID, (SELECT REALNAME FROM TB_USER WHERE ID = ?) AS NAME, CATEGORY, CODE, SALESNUM, SALESTYPE, UNITPRICE, SALESNUM * UNITPRICE AS SALESAMT, REPORTTIME FROM TB_SALESREC WHERE PROMOTOR = ? AND REPORTTIME >= ? AND REPORTTIME < ? ORDER BY REPORTTIME DESC";
	//门店日月销量明细查询
	public static final String SALESREC_QUERY_STORE_DETAIL = "SELECT TS.ID AS ID, TS.CATEGORY AS CATEGORY, TS.CODE AS CODE, TS.SALESNUM AS SALESNUM, TS.SALESTYPE AS SALESTYPE, TS.UNITPRICE AS UNITPRICE, TS.SALESAMT AS SALESAMT, TS.REPORTTIME AS REPORTTIME, TU.REALNAME AS REALNAME FROM(SELECT ID, CATEGORY, CODE, SALESNUM, SALESTYPE, UNITPRICE, SALESNUM * UNITPRICE AS SALESAMT, REPORTTIME, PROMOTOR FROM TB_SALESREC WHERE POINTID = ? AND REPORTTIME >= ? AND REPORTTIME < ? ORDER BY PROMOTOR DESC) TS JOIN (SELECT ID, REALNAME FROM TB_USER) TU ON TU.ID = TS.PROMOTOR";
	
	// 个人销量汇总（当日）
	public static final String SALESREC_QUERY_CURR_DAY = "SELECT SUM(SALESNUM), SUM(DECODE(SALESTYPE,1,SALESNUM)),SUM(DECODE(SALESTYPE,2,SALESNUM)),SUM(DECODE(SALESTYPE,3,SALESNUM)),SUM(SALESNUM*UNITPRICE),SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)),SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)),BRAND,CATEGORY FROM TB_SALESREC WHERE SALESDATE=? AND PROMOTOR = ? AND BRAND = ? AND CATEGORY = ? ";
	public static final String SALESREC_QUERY_DAY_PERSON = "SELECT SUM(SALESNUM) AS NUM, SUM(DECODE(SALESTYPE,1,SALESNUM)) AS NUM1,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS NUM2,SUM(SALESNUM*UNITPRICE) AS AMOUNT1,SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS AMOUNT2,SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS AMOUNT3,(SELECT EVALUE FROM TB_DICT WHERE NAME='BRAND' AND EKEY=BRAND) AS BRAND,(SELECT EVALUE FROM TB_DICT WHERE NAME='CATEGORY' AND EKEY=CATEGORY) AS CATEGORY FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND PROMOTOR = ? ";
	public static final String SALESREC_QUERY_DAY_POINT = "SELECT TS.NUM,TS.AMT,TR.NAME FROM (SELECT MAX(POINTID) AS POINTID,SUM(SALESNUM) AS NUM,SUM(SALESNUM*UNITPRICE) AS AMT FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND POINTID = ? ";
	// 个人每日销量汇总
	public static final String SALESREC_QUERY_PER_DAY = "SELECT SALESDATE, SUM(SALESNUM), SUM(SALESNUM*UNITPRICE),BRAND,CATEGORY FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND PROMOTOR = ? ";
	// 个人每月销量汇总
	public static final String SALESREC_QUERY_PER_MONTH = "SELECT SUM(SALESNUM), SUM(SALESNUM*UNITPRICE),(SELECT EVALUE FROM TB_DICT WHERE NAME='BRAND' AND EKEY=BRAND) AS BRAND,(SELECT EVALUE FROM TB_DICT WHERE NAME='CATEGORY' AND EKEY=CATEGORY) FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND PROMOTOR = ?";
	public static final String SALESREC_QUERY_PER_MONTH_1 = "SELECT YUE, SUM(SALESNUM), SUM(SALESNUM*UNITPRICE),(SELECT EVALUE FROM TB_DICT WHERE NAME='BRAND' AND EKEY=BRAND) AS BRAND,(SELECT EVALUE FROM TB_DICT WHERE NAME='CATEGORY' AND EKEY=CATEGORY) FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND PROMOTOR = ?";
	// 个人机型汇总
	public static final String SALESREC_QUERY_PER_CODE = "SELECT CODE, SUM(SALESNUM) AS NUM, SUM(SALESNUM*UNITPRICE) AS AMOUT,(SELECT EVALUE FROM TB_DICT WHERE NAME='BRAND' AND EKEY=BRAND) AS BRAND,(SELECT EVALUE FROM TB_DICT WHERE NAME='CATEGORY' AND EKEY=CATEGORY) FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND PROMOTOR = ?";
	// 个人销售明细
	public static final String SALESREC_QUERY_DETAIL_PERSON = "SELECT ID,CODE,SALESNUM,UNITPRICE,SALESNUM*UNITPRICE,SALESTYPE,MEMO,REPORTTIME,OPERATE,STANDTYPE,(SELECT EVALUE FROM TB_DICT WHERE NAME='BRAND' AND EKEY=BRAND) AS BRAND,(SELECT EVALUE FROM TB_DICT WHERE NAME='CATEGORY' AND EKEY=CATEGORY) FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND PROMOTOR = ? AND BRAND=? AND CATEGORY=? ";
	public static final String SALESREC_UPD_BY_ID_2 = "UPDATE TB_SALESREC SET SALESNUM=?,UNITPRICE=? WHERE ID=?";
	public static final String FILAILE_USER = "SELECT TP.FILIALE FROM TB_USER TU JOIN TB_POINT TP ON TU.ORG = TP.ID WHERE TU.ID = ?";
	// 根据门店名字在组织表中查询id
	public static final String SALESREC_IMP_POINT = "SELECT ID FROM TB_ORG WHERE NAME = ? AND LEVELS = 5 AND STATE = 1";

	/**
	 * 用户表
	 */
	public static final String USER_UPD_BY_USERID="UPDATE TB_USER SET JOB=? WHERE ID=?";
	public static final String USER_DEL_BY_ID = "UPDATE TB_USER SET DELETED=99 WHERE ID=?";
	public static final String USER_INS = "INSERT INTO TB_USER(ID,NAME,PASS,ORG,REALNAME,PHONENO,EMAIL,DELETED,DESTANDTYPE,DEBRAND,DECATEGORY,OTCATEGORY) VALUES(SEQ_USER.nextval,?,?,?,?,?,?,1,?,?,?,?)";
	public static final String USER_QUERY_BY_ID = "SELECT * FROM TB_USER WHERE ID=?";
	public static final String USER_QUERY_BY_NAME_PASS = "SELECT * FROM TB_USER WHERE PASS=? AND (NAME=? OR MOBILE=?)";
	public static final String USER_QUERY_BY_ORG = "SELECT a.*,b.name as orgname FROM TB_USER a join (SELECT * FROM TB_ORG START WITH ID = ? CONNECT BY parentid = PRIOR ID) b on a.org=b.id ";
	public static final String USER_UPD_BY_ID = "UPDATE TB_USER SET NAME=?,ORG=?,REALNAME=?,PASS=?,PHONENO=?,EMAIL=?,DESTANDTYPE=?,DEBRAND=?,DECATEGORY=?,OTCATEGORY=? WHERE ID=?";
	public static final String USER_UPD_PASS_BY_ID = "UPDATE TB_USER SET PASS=? WHERE ID=?";
	public static final String USER_UPD_BY_ID_ALLOW = "UPDATE TB_USER SET DELETED=1 WHERE ID=?";
	public static final String USER_UPD_BY_ID_DISALLOW = "UPDATE TB_USER SET DELETED=-1 WHERE ID=?";
	public static final String USER_QUERY_REALNAME="SELECT REALNAME FROM TB_USER WHERE ID=?";
	public static final String USER_MONI_INSE="INSERT INTO TB_USER_STA_MONI (id,optname,objname,sysda,opt) VALUES(SEQ_UMONI.nextval,?,?,?,?)";
	/**
	 * 用户角色表
	 */
	public static final String USER_ROLE_DEL_BY_USERID = "DELETE FROM TB_USER_ROLE WHERE USERID=?";
	public static final String USER_ROLE_INS = "INSERT INTO TB_USER_ROLE(ID,USERID,ROLEID,ORGID,DESCRIPTION) VALUES(SEQ_USER_ROLE.nextval,?,?,?,?)";
	public static final String USER_ROLE_UPD_BY_ID = "UPDATE TB_USER_ROLE SET USERID=?,ROLEID=?,ORGID=?,DESCRIPTION=? WHERE ID=?";
//	public static final String USER_ROLE_QUERY_ROLEID_BY_USERID = "SELECT ROLEID,ORGID,USERID,DESCRIPTION FROM TB_USER_ROLE WHERE USERID=? ORDER BY ROLEID,ORGID";
	public static final String USER_ROLE_QUERY_ROLEID_BY_USERID = "SELECT TUR.ROLEID AS ROLEID,TUR.ORGID AS ORGID,TUR.USERID AS USERID,TUR.DESCRIPTION AS DESCRIPTION,TRR.LEVELS AS LEVELS FROM TB_USER_ROLE TUR LEFT JOIN TB_ROLE TRR ON TRR.ID=TUR.ROLEID WHERE TUR.USERID=? ORDER BY TUR.ROLEID,TUR.ORGID";
	
	/**
	 * 报量管理
	 */
	//今日报量管理查询--总部不输入门店名字
	public static final String SALESDAILY_CENTER11 = "SELECT TP.ID AS ID, TP.SHORTNAME AS STORE, TG.NAME AS AREA, TP.PROMONUM AS PROMONUM, " +
			"NVL(TS.SALES,0) AS SALES, TS.RETURNED AS RETURNED FROM (SELECT ID, AREA, SHORTNAME, " +
			"PROMONUM FROM TB_POINT) TP JOIN (SELECT POINTID, SALESTYPE,SUM(DECODE(SIGN(OPERATE-3), -1, SALESNUM)) AS SALES," +
			" SUM(DECODE(OPERATE, 3, SALESNUM)) AS RETURNED FROM TB_SALESREC WHERE SALESDATE = ? AND SALESTYPE=? " +
			"GROUP BY POINTID, SALESTYPE ORDER BY SALES DESC) TS ON TP.ID = TS.POINTID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG " +
			"ON TG.ID = TP.AREA ";
	
	//今日报量管理查询--总部输入门店名字
	public static final String SALESDAILY_CENTER12 = "SELECT TP.ID AS ID, TP.SHORTNAME AS STORE, TG.NAME AS AREA, " +
			"TP.PROMONUM AS PROMONUM, NVL(TS.SALES,0) AS SALES, TS.RETURNED AS RETURNED FROM (SELECT ID, AREA, SHORTNAME, " +
			"PROMONUM FROM TB_POINT WHERE SHORTNAME LIKE ?) TP JOIN (SELECT POINTID, SALESTYPE,SUM(DECODE(SIGN(OPERATE-3), " +
			"-1, SALESNUM)) AS SALES, SUM(DECODE(OPERATE, 3, SALESNUM)) AS RETURNED FROM TB_SALESREC WHERE SALESDATE = ? AND SALESTYPE=? GROUP BY POINTID,SALESTYPE ORDER BY SALES DESC) TS ON TP.ID = TS.POINTID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.AREA ";
	
	//今日报量管理查询--分公司不输入门店名字
	public static final String SALESDAILY_FILIALE21 = "SELECT TP.ID AS ID, TP.SHORTNAME AS STORE, TG.NAME AS AREA, TP.PROMONUM AS PROMONUM, " +
			"NVL(TS.SALES,0) AS SALES, TS.RETURNED AS RETURNED FROM (SELECT ID, AREA, SHORTNAME, PROMONUM " +
			"FROM TB_POINT WHERE FILIALE = ? OR AGENCY = ? OR AREA = ?) TP " +
			" JOIN (SELECT POINTID, SALESTYPE,SUM(DECODE(SIGN(OPERATE-3),-1,SALESNUM)) AS SALES, SUM(DECODE(OPERATE, 3, SALESNUM)) AS RETURNED FROM TB_SALESREC WHERE SALESDATE = ? AND SALESTYPE=? GROUP BY POINTID,SALESTYPE ORDER BY SALES DESC) TS ON TP.ID = TS.POINTID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.AREA ";
	
	//今日报量管理查询--分公司输入门店名字
	public static final String SALESDAILY_FILIALE22 = "SELECT TP.ID AS ID, TP.SHORTNAME AS STORE, TG.NAME AS AREA, " +
			"TP.PROMONUM AS PROMONUM, NVL(TS.SALES,0) AS SALES, TS.RETURNED AS RETURNED FROM (SELECT ID, AREA, " +
			"SHORTNAME, PROMONUM FROM TB_POINT WHERE SHORTNAME LIKE ? AND FILIALE = ? OR AGENCY = ? OR AREA = ?) TP  JOIN (SELECT POINTID, SALESTYPE,SUM(DECODE(SIGN(OPERATE-3),-1,SALESNUM)) AS SALES, SUM(DECODE(OPERATE, 3, SALESNUM)) AS RETURNED FROM TB_SALESREC WHERE SALESDATE = ? AND SALESTYPE=? GROUP BY POINTID,SALESTYPE ORDER BY SALES DESC) TS ON TP.ID = TS.POINTID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.AREA ";
	
	//今日报量管理--查看详细
//	public static final String SALESDAILY_DETAIL = "SELECT TT.ID, TT.SALEMAN, TT.ID, TT.TYPE, SUM(DECODE(SIGN(TSR.SAlESNUM),1,TSR.SALESNUM)), SUM(DECODE(SIGN(TSR.SALESNUM),-1,TSR.SALESNUM)) FROM (SELECT ID, SALEMAN, POINTID, TYPE FROM TB_TERMINAL WHERE POINTID = ?) TT LEFT JOIN (SELECT ID, SALESNUM, TERMINAL FROM TB_SALESREC WHERE SALESDATE = ?) TSR ON TT.ID=TSR.TERMINAL GROUP BY (TT.ID, TT.SALEMAN, TT.TYPE)";
	//public static final String SALESDAILY_DETAIL = "SELECT TS.PROMOTOR AS USERID, TU.REALNAME AS NAME, TU.MOBILE AS MOBILE, TR.NAME AS ROLE, NVL(TS.SALES,0) AS SALES, TS.RETURNED AS RETURNED FROM(SELECT PROMOTOR, SALESTYPE, SUM(DECODE(SIGN(SALESNUM), 1, SALESNUM)) AS SALES, SUM(DECODE(SIGN(SALESNUM), -1, SALESNUM)) AS RETURNED FROM TB_SALESREC WHERE SALESDATE = ? AND POINTID = ? GROUP BY PROMOTOR,SALESTYPE) TS  JOIN (SELECT USERID, ROLEID FROM TB_USER_ROLE) TUR ON TUR.USERID = TS.PROMOTOR  JOIN (SELECT ID, NAME FROM TB_ROLE) TR ON TUR.ROLEID = TR.ID  JOIN (SELECT ID, REALNAME, MOBILE, ORG FROM TB_USER WHERE ORG = ?) TU ON TS.PROMOTOR = TU.ID ";
	public static final String SALESDAILY_DETAIL ="SELECT TS.PROMOTOR AS USERID, TU.REALNAME AS NAME, "
		+ " TTMI.TERMINAL AS MOBILE,TR.NAME AS ROLE, NVL(TS.SALES,0) AS SALES, TS.RETURNED AS RETURNED " + "FROM(SELECT PROMOTOR, SUM(DECODE(SIGN(OPERATE-3),-1,SALESNUM)) AS SALES, "
		+ " SUM(DECODE(OPERATE, 3, SALESNUM)) AS RETURNED "
		+ " FROM TB_SALESREC WHERE SALESTYPE=? AND SALESDATE = ? AND POINTID = ? GROUP BY PROMOTOR) TS LEFT JOIN (SELECT ID, REALNAME, MOBILE, ORG FROM TB_USER) TU ON TS.PROMOTOR = TU.ID "
		+ " LEFT JOIN (SELECT USERID, ROLEID FROM TB_USER_ROLE) TUR ON TUR.USERID = TS.PROMOTOR " + " LEFT JOIN (SELECT ID, NAME FROM TB_ROLE) TR ON TUR.ROLEID = TR.ID  " + "  "
		+ " LEFT JOIN (SELECT ID, TERMINAL, SALEMAN FROM TB_TERMINAL) TTMI ON TTMI.SALEMAN=TS.PROMOTOR ORDER BY SALES DESC";
	//今日报量--查看报量
	public static final String SALESDAILY_SALES = "SELECT ID, CODE, UNITPRICE, SALESNUM, MEMO, REPORTTIME FROM TB_SALESREC WHERE POINTID=? AND PROMOTOR = ? AND SALESDATE = ? AND OPERATE < 3";
	
	//今日报量--退货明细
	public static final String SALESDAILY_RETURN = "SELECT ID, CODE, UNITPRICE, SALESNUM, MEMO, REPORTTIME FROM TB_SALESREC WHERE POINTID=? AND PROMOTOR = ? AND SALESDATE = ? AND SALESNUM < 0 AND OPERATE = 3";
	
	

	/**
	 * 销量统计
	 */
	//销量统计--总部 1全部,2选择具体
	public static final String SALES_STATIS_CENTER11 = "SELECT TP.FILIALE, TG.NAME, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM ((SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) UNION ALL (SELECT POINTID, SUM(DECODE(SALESTYPE,1,SALESNUM)) AS RETAILNUM, SUM(DECODE(SALESTYPE,2,SALESNUM)) AS BATCHNUM, SUM(DECODE(SALESTYPE,3,SALESNUM)) AS ENGINNUM, SUM(SALESNUM) AS WHOLENUM, SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS RETAILAMT, SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS BATCHAMT, SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS ENGINAMT, SUM(SALESNUM*UNITPRICE) AS WHOLEAMT FROM TB_SALESREC WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID)) TPP RIGHT JOIN (SELECT ID, FILIALE FROM TB_POINT) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.FILIALE GROUP BY TP.FILIALE, TG.NAME";
	public static final String SALES_STATIS_CENTER22 = "SELECT TP.FILIALE, TG.NAME, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM ((SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) UNION ALL (SELECT POINTID, SUM(DECODE(SALESTYPE,1,SALESNUM)) AS RETAILNUM, SUM(DECODE(SALESTYPE,2,SALESNUM)) AS BATCHNUM, SUM(DECODE(SALESTYPE,3,SALESNUM)) AS ENGINNUM, SUM(SALESNUM) AS WHOLENUM, SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS RETAILAMT, SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS BATCHAMT, SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS ENGINAMT, SUM(SALESNUM*UNITPRICE) AS WHOLEAMT FROM TB_SALESREC WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID)) TPP RIGHT JOIN (SELECT ID, FILIALE FROM TB_POINT WHERE FILIALE = ?) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.FILIALE GROUP BY TP.FILIALE, TG.NAME";
	public static final String SALES_STATIS_CENTER1 = "SELECT TP.FILIALE, TG.NAME, nvl(SUM(RETAILNUM),0) AS RETAILNUM, nvl(SUM(BATCHNUM),0) AS BATCHNUM, nvl(SUM(ENGINNUM),0) AS ENGINNUM, nvl(SUM(WHOLENUM),0) AS WHOLENUM, nvl(SUM(RETAILAMT),0) AS RETAILAMT,nvl(SUM(BATCHAMT),0) AS BATCHAMT, nvl(SUM(ENGINAMT),0) AS ENGINAMT,nvl(SUM(WHOLEAMT),0) AS WHOLEAMT FROM (SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) TPP RIGHT JOIN (SELECT ID, FILIALE FROM TB_POINT) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.FILIALE GROUP BY TP.FILIALE, TG.NAME";
	public static final String SALES_STATIS_CENTER2 = "SELECT TP.FILIALE, TG.NAME, nvl(SUM(RETAILNUM),0) AS RETAILNUM, nvl(SUM(BATCHNUM),0) AS BATCHNUM, nvl(SUM(ENGINNUM),0) AS ENGINNUM, nvl(SUM(WHOLENUM),0) AS WHOLENUM, nvl(SUM(RETAILAMT),0) AS RETAILAMT,nvl(SUM(BATCHAMT),0) AS BATCHAMT, nvl(SUM(ENGINAMT),0) AS ENGINAMT,nvl(SUM(WHOLEAMT),0) AS WHOLEAMT FROM (SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) TPP RIGHT JOIN (SELECT ID, FILIALE FROM TB_POINT WHERE FILIALE = ?) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.FILIALE GROUP BY TP.FILIALE, TG.NAME";

	//销量统计--分公司 1全部,2选择具体
	public static final String SALES_STATIS_FILIALE11 = "SELECT TP.AGENCY AS AGENCY,TG.NAME AS NAME, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM ((SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) UNION ALL (SELECT POINTID, SUM(DECODE(SALESTYPE,1,SALESNUM)) AS RETAILNUM, SUM(DECODE(SALESTYPE,2,SALESNUM)) AS BATCHNUM, SUM(DECODE(SALESTYPE,3,SALESNUM)) AS ENGINNUM, SUM(SALESNUM) AS WHOLENUM, SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS RETAILAMT, SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS BATCHAMT, SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS ENGINAMT, SUM(SALESNUM*UNITPRICE) AS WHOLEAMT FROM TB_SALESREC WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID)) TPP RIGHT JOIN (SELECT ID, AGENCY FROM TB_POINT WHERE FILIALE = ?) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.AGENCY GROUP BY TP.AGENCY, TG.NAME";
	public static final String SALES_STATIS_FILIALE22 = "SELECT TP.AGENCY AS AGENCY, TG.NAME AS NAME, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM ((SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) UNION ALL (SELECT POINTID, SUM(DECODE(SALESTYPE,1,SALESNUM)) AS RETAILNUM, SUM(DECODE(SALESTYPE,2,SALESNUM)) AS BATCHNUM, SUM(DECODE(SALESTYPE,3,SALESNUM)) AS ENGINNUM, SUM(SALESNUM) AS WHOLENUM, SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS RETAILAMT, SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS BATCHAMT, SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS ENGINAMT, SUM(SALESNUM*UNITPRICE) AS WHOLEAMT FROM TB_SALESREC WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID)) TPP RIGHT JOIN (SELECT ID, AGENCY FROM TB_POINT WHERE AGENCY = ?) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.AGENCY GROUP BY TP.AGENCY, TG.NAME";
	public static final String SALES_STATIS_FILIALE1 = "SELECT TP.AGENCY AS AGENCY,TG.NAME AS NAME, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM (SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) TPP RIGHT JOIN (SELECT ID, AGENCY FROM TB_POINT WHERE FILIALE = ?) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.AGENCY GROUP BY TP.AGENCY, TG.NAME";
	public static final String SALES_STATIS_FILIALE2 = "SELECT TP.AGENCY AS AGENCY,TG.NAME AS NAME, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM (SELECT POINTID, SUM(RETAILNUM) AS RETAILNUM, SUM(BATCHNUM) AS BATCHNUM, SUM(ENGINNUM) AS ENGINNUM, SUM(WHOLENUM) AS WHOLENUM, SUM(RETAILAMT) AS RETAILAMT,SUM(BATCHAMT) AS BATCHAMT, SUM(ENGINAMT) AS ENGINAMT,SUM(WHOLEAMT) AS WHOLEAMT FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) TPP RIGHT JOIN (SELECT ID, AGENCY FROM TB_POINT WHERE AGENCY = ?) TP ON TPP.POINTID = TP.ID LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG ON TG.ID = TP.AGENCY GROUP BY TP.AGENCY, TG.NAME";
	
	/**
	 * 上报终止表
	 */
	public static final String REPORTREC_INS_BY_TERMINALID = "INSERT INTO TB_REPORTREC(ID,POINTID,TERMINAL,RETAILNUM,BATCHNUM,ENGINNUM,RETAILAMT,BATCHAMT,ENGINAMT,WHOLENUM,WHOLEAMT,RETURNNUM,RETURNAMT,ENDTIME,SALESDATE) " +
		"SELECT SEQ_REPORTREC.nextval,A,B,C,D,E,F,G,H,I,J,K,L,M,N FROM(SELECT MAX(POINTID)AS A,MAX(?)AS B,SUM(DECODE(SALESTYPE,1,SALESNUM)) AS C,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS D,SUM(DECODE(SALESTYPE,3,SALESNUM)) AS E,SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS F,SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS G,SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS H,MAX(?) AS I,MAX(?) AS J,MAX(?) AS K,MAX(?) AS L,MAX(?) AS M,MAX(SALESDATE) AS N FROM TB_SALESREC WHERE TERMINAL=?)";
	public static final String REPORTREC_QUERY="SELECT * FROM TB_REPORTREC WHERE TERMINAL=? AND ENDTIME BETWEEN to_date(?,'yyyy-mm-dd hh24:mi:ss') AND to_date(?,'yyyy-mm-dd hh24:mi:ss')";
//往上报终止表中插入数据的同时，更新TB_SALESREC中的SALASDATE字段
	public static final String SALESREC_UPDATE_SALESDATE = "UPDATE TB_SALESREC SET SALESDATE=? WHERE TERMINAL=? AND REPORTTIME>? AND REPORTTIME<=? AND SALESDATE IS NULL ";
	public static final String SALESREC_QUERY_REPORTTIME ="SELECT * FROM TB_SALESREC WHERE TERMINAL=? AND REPORTTIME BETWEEN TO_DATE(?,'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(?,'yyyy-mm-dd hh24:mi:ss') ORDER BY REPORTTIME DESC";
	
	/**
	 * 门店每日销量表
	 */
	//手动 终止上报，暂不使用
	public static final String TERMINALDAILY_INS_BY_POINTID="INSERT INTO TB_TERMINALDAILY(ID,POINTID,TERMINAL,SALESDATE,ENDTIME,RETAILNUM,BATCHNUM,ENGINNUM,RETURNNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,RETURNAMT,WHOLEAMT) " +
		"SELECT SEQ_TERMINALDAILY.nextval,?,TT.ID,?,?,TT.C,TT.D,TT.E,TT.EEE,TT.EE,TT.F,TT.G,TT.H,TT.HHH,TT.HH FROM(SELECT * FROM TB_TERMINAL TE LEFT JOIN (SELECT TERMINAL AS B,SUM(DECODE(SALESTYPE,1,SALESNUM)) AS C,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS D,SUM(DECODE(SALESTYPE,3,SALESNUM)) AS E,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM)) AS EEE,SUM(SALESNUM) AS EE,SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS F,SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS G,SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS H,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM*UNITPRICE)) AS HHH,SUM(SALESNUM*UNITPRICE) AS HH FROM TB_SALESREC WHERE SALESDATE =? GROUP BY TERMINAL) TS ON TE.ID=TS.B WHERE TE.POINTID=?) TT";
	public static final String POINTDAILY_QUERY="SELECT * FROM TB_POINTDAILY WHERE POINTID=? AND ENDTIME >= ? AND ENDTIME < ?";	
	public static final String TERMINAL_COUNT="SELECT * FROM TB_TERMINAL WHERE POINTID=?";
	public static final String POINTDAILY_INS_BY_POINTID="INSERT INTO TB_POINTDAILY(ID,POINTID,SALESDATE,ENDTIME,RETAILNUM,BATCHNUM,ENGINNUM,RETURNNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,RETURNAMT,WHOLEAMT,BYTERMINAL) " +
		"SELECT SEQ_POINTDAILY.nextval,?,?,?,C,D,E,EEE,EE,F,G,H,HHH,HH,? FROM(SELECT SUM(DECODE(SALESTYPE,1,SALESNUM)) AS C,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS D,SUM(DECODE(SALESTYPE,3,SALESNUM)) AS E,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM)) AS EEE,SUM(SALESNUM) AS EE,SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS F,SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS G,SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS H,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM*UNITPRICE)) AS HHH,SUM(SALESNUM*UNITPRICE) AS HH FROM TB_SALESREC WHERE SALESDATE=? AND POINTID=?)";
	//门店每日汇总 暂不使用
	public static final String TERMINALDAILY_INS = "INSERT INTO TB_TERMINALDAILY(ID,POINTID,TERMINAL,SALESDATE,ENDTIME,RETAILNUM,BATCHNUM,ENGINNUM,RETURNNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,RETURNAMT,WHOLEAMT) " +
			"SELECT SEQ_TERMINALDAILY.nextval,TT.POINTID,TT.ID,?,?,TT.C,TT.D,TT.E,TT.EEE,TT.EE,TT.F,TT.G,TT.H,TT.HHH,TT.HH FROM(SELECT * FROM TB_TERMINAL TE RIGHT JOIN (SELECT TERMINAL AS B,SUM(DECODE(SALESTYPE,1,SALESNUM)) AS C,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS D,SUM(DECODE(SALESTYPE,3,SALESNUM)) AS E,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM)) AS EEE,SUM(SALESNUM) AS EE,SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS F,SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS G,SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS H,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM*UNITPRICE)) AS HHH,SUM(SALESNUM*UNITPRICE) AS HH FROM TB_SALESREC WHERE SALESDATE =? GROUP BY TERMINAL) TS ON TE.ID=TS.B WHERE TE.POINTID NOT IN (SELECT POINTID FROM TB_POINTDAILY WHERE SALESDATE = ?)) TT";	
	public static final String POINTDAILY_INS = "INSERT INTO TB_POINTDAILY(ID,POINTID,SALESDATE,ENDTIME,RETAILNUM,BATCHNUM,ENGINNUM,RETURNNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,RETURNAMT,WHOLEAMT) " +
			"SELECT SEQ_POINTDAILY.nextval,POINTID,?,?,C,D,E,EEE,EE,F,G,H,HHH,HH FROM(SELECT POINTID,SUM(DECODE(SALESTYPE,1,SALESNUM)) AS C,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS D,SUM(DECODE(SALESTYPE,3,SALESNUM)) AS E,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM)) AS EEE,SUM(SALESNUM) AS EE,SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE)) AS F,SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS G,SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE)) AS H,SUM(DECODE(SIGN(SALESNUM),-1,-SALESNUM*UNITPRICE)) AS HHH,SUM(SALESNUM*UNITPRICE) AS HH FROM TB_SALESREC WHERE SALESDATE=? AND POINTID NOT IN (SELECT POINTID FROM TB_POINTDAILY WHERE SALESDATE = ?) GROUP BY POINTID)";
	
	/**
	 * 报量情况查看
	 */
	//终端报量查看
	public static final String SALES_REPORT_TERMINAL = "SELECT TG1.NAME AS FILIALE, TG2.NAME AS AGENCY, TG3.NAME AS AREA, TP.SHORTNAME AS STORE, TT.SALEMAN AS SALEMAN, TT.ID AS TERMINALID, TTD.WNUM AS WHOLENUM, TTDW.WNUM AS TTDWNUM, TPD.ZNUM AS ZHILINGNUM, TPD.PNUM AS PILINGNUM,TPD.WNUM AS TPDWNUM "
	+ " FROM (SELECT ID, POINTID, SALEMAN FROM TB_TERMINAL) TT "
	+ " JOIN (SELECT ID, FILIALE, AGENCY, AREA, SHORTNAME FROM TB_POINT WHERE TERMINALNUM != 0) TP ON TP.ID = TT.POINTID " 
	+ " LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG1 ON TG1.ID = TP.FILIALE " 
	+ " LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG2 ON TG2.ID = TP.AGENCY "
	+ " LEFT JOIN (SELECT ID, NAME FROM TB_ORG) TG3 ON TG3.ID = TP.AREA "
	+ " LEFT JOIN (SELECT POINTID, SUM(ZHILINGNUM) AS ZNUM, SUM(PILINGNUM) AS PNUM,SUM(WHOLENUM) AS WNUM " 
	+ " FROM V_POINTDAILY WHERE SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) TPD ON TT.POINTID = TPD.POINTID "
	+ " LEFT JOIN (SELECT POINTID, SUM(WHOLENUM) AS WNUM FROM V_TERMINALDAILY WHERE SALESDATE>=? "
	+ " AND SALESDATE<? GROUP BY POINTID) TTDW ON TT.POINTID = TTDW.POINTID "
	+ " LEFT JOIN (SELECT POINTID, SUM(WHOLENUM) AS WNUM, TERMINAL FROM V_TERMINALDAILY WHERE SALESDATE>=? " 
	+ " AND SALESDATE<? GROUP BY TERMINAL, POINTID) TTD ON TT.ID = TTD.TERMINAL ";
	
	//无终端报量
	public static final String SALES_REPORT_POINT = "SELECT * FROM(SELECT TG1.NAME AS FILIALE, " +
			"TG2.NAME AS AGENCY, TG3.NAME AS AREA, TP.SHORTNAME AS STORE, NVL(TPD.PNUMS,0) AS WHOLENUM, " +
			"TP.MKLEVEL AS MKLEVEL, TP.PIPETYPE1 AS PIPETYPE1, TP.PIPETYPE2 AS PIPETYPE2 " +
			"FROM (SELECT ID, FILIALE, AGENCY, AREA, SHORTNAME, MKLEVEL, PIPETYPE1, PIPETYPE2 FROM TB_POINT WHERE TERMINALNUM = 0) TP " +
			" JOIN (SELECT ID, NAME FROM TB_ORG) TG1 ON TG1.ID = TP.FILIALE " +
			" JOIN (SELECT ID, NAME FROM TB_ORG) TG2 ON TG2.ID = TP.AGENCY " +
			" JOIN (SELECT ID, NAME FROM TB_ORG) TG3 ON TG3.ID = TP.AREA " +
			" JOIN (SELECT POINTID, SUM(SALESNUM) AS PNUMS FROM TB_SALESREC WHERE TERMINAL IS NULL " +
			"AND SALESDATE>=? AND SALESDATE<? GROUP BY POINTID) TPD ON TP.ID = TPD.POINTID) ORDER BY WHOLENUM DESC";
	
	/**
	 * 样机管理
	 */
	//样机上报
	public static final String SAMPLE_OUT_ADD = "INSERT INTO TB_SAMPLEREC(ID,BRAND,CATEGORY,CODE, POINTID, TERMINAL, PROMOTOR, SAMPLENO, STATUS, OUTTIME, OUTMEMO, SOURCE) VALUES (SEQ_SAMPLEREC.nextval,?,?,?,?,?,?,?,?,?,?,?)";
	//摆样查询
	public static final String SAMPLE_OUT_QUERY = "SELECT CODE, SUM(1) FROM TB_SAMPLEREC WHERE POINTID = ? AND STATUS = 1 GROUP BY CODE";
	public static final String SAMPLE_OUT_USERINFO = "SELECT TU.REALNAME, TU.PHONENO, TP.SHORTNAME, TP.ID FROM (SELECT REALNAME, PHONENO, ORG FROM TB_USER WHERE ID = ?) TU LEFT JOIN (SELECT ID, SHORTNAME FROM TB_POINT) TP ON TP.ID = TU.ORG ";
	//根据门店查询样机总数
	public static final String SAMPLE_OUT_QUERY_COUNT = "SELECT COUNT(1) FROM TB_SAMPLEREC WHERE POINTID = ? AND STATUS = 1";
	//根据型号获取样机详细信息
	public static final String SAMPLE_OUT_QUERY_CODE = "SELECT CODE, SAMPLENO,OUTTIME FROM TB_SAMPLEREC WHERE POINTID = ? AND CODE = ? AND STATUS = 1";
	//样机撤样
	public static final String SAMPLE_CANCEL = "UPDATE TB_SAMPLEREC SET STATUS = -1, CANCELWAY = ?, CANCELTIME = ?, CANCELMEMO = ? WHERE SAMPLENO = ?";
	//样机撤样查询
	public static final String SAMPLE_CANCEL_QUERY = "SELECT * FROM (SELECT TS.ID AS ID,TS.CODE AS CODE,TS.SAMPLENO AS SAMPLENO,TS.OUTTIME AS OUTTIME,TS.CANCELTIME AS CANCELTIME,TS.CANCELWAY AS CANCELWAY,TD.EVALUE AS BRAND,TDD.EVALUE AS CATEGORY FROM (SELECT ID, CODE,BRAND,CATEGORY, SAMPLENO, OUTTIME, CANCELTIME, CANCELWAY " +
			" FROM TB_SAMPLEREC WHERE POINTID = ? AND STATUS = -1 ORDER BY CANCELTIME DESC) TS LEFT JOIN (SELECT NAME,EKEY,EVALUE,ID FROM TB_CASC WHERE NAME='BRAND') TD ON TD.EKEY=TS.BRAND LEFT JOIN (SELECT NAME,EKEY,EVALUE,ID,PK FROM TB_CASC WHERE NAME='BRAND_CATEGORY') TDD ON TDD.EKEY=TS.CATEGORY ) WHERE ROWNUM <= 5";
	public static final String SAMPLE_CANCEL_QUERY_USERINFO = "SELECT REALNAME, MOBILE FROM TB_USER WHERE ID = ?";
	public static final String SAMPLE_CANCEL_QUERY_STOREINFO = "SELECT NAME FROM TB_ORG WHERE ID = ?";
	//样机修改
	public static final String SAMPLEREC_UPDATE = "UPDATE TB_SAMPLEREC SET BRAND = ?,CATEGORY = ?,CODE=?, SAMPLENO=?, CANCELWAY=? WHERE ID=?";
	//样机删除
	public static final String SAMPLEREC_DEL_BY_ID = "DELETE FROM TB_SAMPLEREC WHERE ID=?";
	//根据门店获取样机型号
	public static final String SAMPLEREC_CODE_BY_POINT = "SELECT CODE FROM TB_SAMPLEREC WHERE POINTID = ? AND STATUS = 1 GROUP BY CODE";
	public static final String SAMPLEREC_ATTACH_BY_POINT="SELECT DISTINCT ATTACH FROM TB_SAMPLEREC WHERE POINTID=? AND STATUS=1";
	/**
	 * 店面回款数据记录表
	 */
	public static final String POINTPAY_ADD="INSERT INTO TB_POINTPAY(ID,FILIALEID,AGENCYID,AREAID,POINTID,POINTNAME,DIRECTTYPE,DIRECT,PAYAMOUNT,PAYDATE,MODDATE,MODSTATE) VALUES (SEQ_POINTPAY.nextval,?,?,?,?,?,?,?,?,TO_DATE(?,'yyyy-mm-dd'),?,?)";
	public static final String POINTPAY_QUERY="SELECT ID, ZONE1, ZONE2, FILIALE, AGENCY, AREA, SHORTNAME FROM TB_POINT";
	public static final String POINT_QUERY_ALL="SELECT * FROM TB_POINT WHERE FILIALE=? OR AGENCY=? OR AREA=?";
	public static final String POINT_QUERY_BY_SHORTNAME="SELECT * FROM TB_POINT WHERE SHORTNAME=? AND STATE = 1";
	public static final String POINTPAY_DEL_BY_ID="DELETE FROM TB_POINTPAY WHERE ID=? AND MODSTATE=0 AND PAYDATE BETWEEN ? AND ?";
	public static final String POINTPAY_UPD_BY_ID="UPDATE TB_POINTPAY SET ZONE1ID=?,ZONE2ID=?,FILIALEID=?,AGENCYID=?,AREAID=?,POINTID=?,POINTNAME=?,DIRECTTYPE=?,DIRECT=?,PAYAMOUNT=?,PAYDATE=?,MODDATE=?,MODSTATE=? WHERE ID=?";
	public static final String ORG_QUERY_NAME_BY_ID="SELECT * FROM TB_ORG WHERE ID=?";
	public static final String POINTPAY_GROUP_BY_FILIALEID="SELECT MAX(ZONE1ID),MAX(ZONE2ID),FILIALEID,SUM(PAYAMOUNT) FROM TB_POINTPAY GROUP BY FILIALEID";
	public static final String POINTPAY_SET_MODSTATE="UPDATE TB_POINTPAY SET MODSTATE=1 WHERE PAYDATE BETWEEN TO_DATE(?,'yyyy-mm-dd') AND TO_DATE(?,'yyyy-mm-dd')";
	public static final String POINTPAY_QUERY_BY_ID="SELECT * FROM TB_POINTPAY WHERE ID=? AND MODSTATE=0 AND PAYDATE BETWEEN ? AND ?";
	public static final String POINT_QUERY_ID="SELECT * FROM TB_POINT WHERE FILIALE =(SELECT FILIALE FROM TB_POINT WHERE SHORTNAME=?) AND SHORTNAME<>?";
	public static final String ORG_QUERY_ID_BY_NAME="SELECT ID FROM TB_ORG WHERE NAME=?";
	
	/**
	 * 分公司回款数据汇总表
	 */
	public static final String FILIALEPAY_INS = "INSERT INTO TB_FILIALEPAY(ID,ZONE1ID,ZONE2ID,FILIALEID,FILIALENAME,PAYAMOUNT,PAYDATE,ENDTIME) "+
	"SELECT SEQ_FILIALEPAY.nextval,T.ZONE1,T.ZONE2,T.FILIALE,T.NAME,T.PAYAMOUNT,TO_DATE(?,'yyyy-mm'),? FROM(SELECT P.NAME,P.ZONE1,P.ZONE2,FILIALE,PP.PAYAMOUNT FROM (SELECT OO.NAME,DD.ZONE1,DD.ZONE2,DD.FILIALE FROM(SELECT DISTINCT ZONE1,ZONE2,FILIALE FROM TB_POINT) DD JOIN TB_ORG OO ON DD.FILIALE=OO.ID) P LEFT JOIN (SELECT MAX(ZONE1ID) AS ZONE1ID,MAX(ZONE2ID) AS ZONE2ID,FILIALEID,SUM(PAYAMOUNT) AS PAYAMOUNT FROM TB_POINTPAY WHERE PAYDATE BETWEEN TO_DATE(?,'yyyy-mm-dd') AND TO_DATE(?,'yyyy-mm-dd') GROUP BY FILIALEID) PP ON P.FILIALE=PP.FILIALEID) T";
	public static final String FILIALEPAY_QUERY_BY_PAYDATE="SELECT * FROM TB_FILIALEPAY WHERE PAYDATE = TO_DATE(?,'yyyy-mm')";
	public static final String FILIALEPAY_QUERY_UNPAY="SELECT * FROM TB_FILIALEPAY WHERE PAYAMOUNT IS NULL AND PAYDATE=TO_DATE(?,'yyyy-mm')";
	
	/**
	 * 查询模版
	 */
	public static final String QUERYTEMPLATE_ADD = "INSERT INTO TB_QUERYTEMPLATE(ID, PAGE, TNAME, TVALUE, USERCODE) VALUES (SEQ_QUERYTEMPLATE.nextval,?,?,?,?)";
	public static final String QUERYTEMPLATE_DEL_BY_ID = "DELETE FROM TB_QUERYTEMPLATE WHERE ID=?";
	public static final String QUERYTEMPLATE_GETS = "SELECT ID,TNAME FROM TB_QUERYTEMPLATE WHERE USERCODE=? AND PAGE=?";
	public static final String QUERYTEMPLATE_GET_BY_ID = "SELECT ID,PAGE,TNAME,TVALUE,USERCODE FROM TB_QUERYTEMPLATE WHERE ID=?";
	
	/**
	 * 配件管理
	 */
	//总部新增
	public static final String ATTACH_INS = "INSERT INTO TB_ATTACH(ID, NAME, CODE, COSTPRICE, FORPRICE, RECORDTIME,SALESPRICE) VALUES (SEQ_ATTACH.nextval,?,?,?,?,?,?)";
	//删除
	public static final String ATTACH_DEL_BY_ID_HQ = "DELETE FROM TB_ATTACH WHERE ID=?";
	public static final String ATTACH_DEL_BY_ID_FI = "DELETE FROM TB_ATTACH_FILIALE WHERE CODE = (SELECT CODE FROM TB_ATTACH WHERE ID=?)";
	//更新
	public static final String ATTACH_UPD_BY_ID_HQ = "UPDATE TB_ATTACH SET NAME=?, CODE=?, COSTPRICE=?, FORPRICE=?, SALESPRICE=?,RECORDTIME=? WHERE ID=?";
	public static final String ATTACH_UPD_BY_ID_FI = "UPDATE TB_ATTACH_FILIALE SET NAME=?,CODE=?, SALESPRICE=? WHERE CODE=(SELECT CODE FROM TB_ATTACH WHERE ID=?)";
	//查询所有配件名称
	public static final String ATTACH_QUERY_NAMES = "SELECT ID, NAME FROM TB_ATTACH";
	//总部配件id查询配件信息
	public static final String ATTACH_QUERY_BY_ID_HQ = "SELECT TA.ID, TA.NAME, TA.CODE, TAF.SALESPRICEHQ, TA.COSTPRICE, TA.FORPRICE FROM (SELECT * FROM TB_ATTACH WHERE ID = ?) TA LEFT JOIN TB_ATTACH_FILIALE TAF ON TA.ID = TAF.ID";
	//查询所有的配件型号
	public static final String ATTACH_QUERY_CODES = "SELECT CODE FROM TB_ATTACH GROUP BY CODE";
	//分公司根据id获取配件信息
	public static final String ATTACH_QUERY_BY_ID_FI = "SELECT TA.ID,TA.NAME,TA.CODE,TA.SALESPRICE,TAF.SALESPRICE FROM (SELECT ID,NAME,CODE,SALESPRICE FROM TB_ATTACH_FILIALE WHERE ID=?) TA JOIN TB_ATTACH_FILIALE ON TA.CODE=TAF.CODE";
	//根据配件名称获取型号
	public static final String ATTACH_QUERY_CODES_BY_NAME = "SELECT NAME,CODE FROM TB_ATTACH WHERE NAME = ? ";
	public static final String ATTACH_FILIALE_UPD = "UPDATE TB_ATTACH_FILIALE SET SALESPRICE=?,RECORDTIME=? WHERE ID=?";
	//导入配件数据
	public static final String PARTS_INS = "INSERT INTO TB_ATTACH VALUES(SEQ_ATTACH.nextval,?,?,?,?,?,?)";
	//导入配件数据（分公司）
	public static final String ATTACH_QUERY_ALL = "SELECT NAME,CODE,SALESPRICE,RECORDTIME FROM TB_ATTACH";
	public static final String ATTACH_FILIALE_INSERT="INSERT INTO TB_ATTACH_FILIALE(ID,FILIALE,NAME,CODE,SALESPRICE,RECORDTIME) VALUES(SEQ_TB_ATTACH_FILIALE.nextval,?,?,?,?,?)";
	public static final String ATTACH_EXPORT="SELECT NAME,CODE,COSTPRICE,FORPRICE,SALESPRICE FROM TB_ATTACH";
	/**
	 * 参数设置(空调没有字段LINGXLTIME)
	 */
	public static final String PARAM_IN = "INSERT INTO TB_PARAM(ID, ORG, TERMETIME, WEBSTIME, WEBETIME, CASHEDATE,WORKTIME) VALUES (SEQ_PARAM.nextval,?,?,?,?,?,?)";
	public static final String PARAM_UPDATE = "UPDATE TB_PARAM SET TERMETIME=?, WEBSTIME=?, WEBETIME=?, CASHEDATE=?,WORKTIME=?,LINGXLTIME=? WHERE ORG = ?";
	public static final String PARAM_QUERY_BY_ORG = "SELECT ID, ORG, TERMETIME, WEBSTIME, WEBETIME, CASHEDATE,WORKTIME FROM TB_PARAM WHERE ORG = ?";
	
	/**
	 * 公告
	 */
	public static final String ANNOUNCEMENT_INS = "INSERT INTO TB_ANNOUNCEMENT(ID,PUBLISHER,PUBLISHORG,TITLE,ANNOUNCETIME,VALIDITYDATE,CONTENT) VALUES (SEQ_ANNOUNCEMENT.nextval,?,?,?,?,?,?) ";
	public static final String ORG_QUERY_PARENTID = "SELECT * FROM TB_ORG WHERE ID=?";
	public static final String ANNOUNCEMENT_DEL_BY_ID = "DELETE FROM TB_ANNOUNCEMENT WHERE ID=?";
	public static final String ANN_ORG_DEL_BY_ID = "UPDATE TB_ANN_ORG SET DELETED = -1 WHERE ANNID=? AND ORGID=?";
	public static final String ANN_USER_DEL_BY_ID = "DELETE FROM TB_ANN_USER WHERE ANNID=? AND ORGID=?";
	public static final String ANNOUNCEMENT_UPDATE_BY_ID = "UPDATE TB_ANNOUNCEMENT SET TITLE=?,VALIDITYDATE=?,CONTENT=? WHERE ID=?";
	public static final String ANNOUNCEMENT_QUERY_ID = "SELECT MAX(ID) FROM TB_ANNOUNCEMENT";
	public static final String ANNOUNCEMENT_QUERY_BY_ID = "SELECT A.ID,B.NAME,A.TITLE,A.ANNOUNCETIME,A.VALIDITYDATE,A.CONTENT FROM TB_ANNOUNCEMENT A,TB_USER B WHERE A.ID=? AND A.PUBLISHER=B.ID";
	public static final String ANN_ORG_INS = "INSERT INTO TB_ANN_ORG(ID, ORGID, ANNID) VALUES (SEQ_ANN_ORG.nextval,?,?)";
	public static final String ANN_USER_INS = "INSERT INTO TB_ANN_USER(ID,ANNID,ORGID,USERID,READDATE) VALUES (SEQ_ANN_USER.nextval,?,?,?,?)";
	
	/**
	 *终端查看公告
	 */
	public static final String TERMINAL_ANN_INS="INSERT INTO TB_TERMINAL_ANN(ID,ANNID,TERMINAL,USERID,USERNAME,POINTID,AREAID,READED) "+
		"SELECT SEQ_TERMINAL_ANN.nextval,TT.ANNID,TT.TERMINAL,?,TT.USERNAME,TT.POINTID,TT.AREAID,? FROM (SELECT TP.ID AS TERMINAL,TP.SALEMAN AS USERNAME,TP.POINTID,TP.AREA AS AREAID,A.ID AS ANNID FROM (SELECT T.ID,T.SALEMAN,P.ID AS POINTID,P.AREA FROM TB_TERMINAL T RIGHT JOIN TB_POINT P ON T.POINTID = P.ID) TP LEFT JOIN TB_ANNOUNCEMENT A ON TP.AREA = A.AREAID WHERE A.ID=?) TT";
	public static final String TERMINAL_ANN_QUERY_READ="SELECT * FROM TB_TERMINAL_ANN WHERE ANNID=? AND READED=1";
	public static final String TERMINAL_ANN_UPDATE="UPDATE TB_TERMINAL_ANN SET READED=1 WHERE TERMINAL =? AND ANNID=?"; 
	
	/**
	 *日点评
	 */
	public static final String SALESREC_QUERY_BY_CODE ="SELECT TP.CODE, NVL(TS.NUM,0) FROM TB_PRODUCT TP LEFT JOIN (SELECT CODE,SUM(SALESNUM) AS NUM FROM TB_SALESREC WHERE SALESDATE=? GROUP BY CODE) TS ON TP.CODE=TS.CODE";
	public static final String SALESREC_QUERY_BY_FILIALE="SELECT TBO.NAME,TTP.SALES FROM (SELECT TP.FILIALE,SUM(TT.SALES) AS SALES FROM (SELECT SUM(SALESNUM) AS SALES,POINTID FROM TB_SALESREC WHERE SALESDATE=? AND CODE=? GROUP BY POINTID) TT JOIN TB_POINT TP ON TT.POINTID=TP.ID GROUP BY TP.FILIALE) TTP JOIN TB_ORG TBO ON TTP.FILIALE=TBO.ID ";
	public static final String SALESREC_QUERY_BY_CODE_MON ="SELECT TP.CODE, NVL(TS.NUM,0) FROM TB_PRODUCT TP LEFT JOIN (SELECT CODE,SUM(SALESNUM) AS NUM FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? GROUP BY CODE) TS ON TP.CODE=TS.CODE";
	public static final String SALESREC_QUERY_BY_FILIALE_MON="SELECT RR.NAME,RR.SALES FROM (SELECT Q.NAME AS PARENT,R.NAME FROM TB_ORG Q JOIN TB_ORG R ON Q.ID= R.PARENTID) EE JOIN (SELECT TBO.NAME,TTP.FILIALE,TTP.SALES FROM (SELECT TP.FILIALE,SUM(TT.SALES) AS SALES FROM (SELECT SUM(SALESNUM) AS SALES,POINTID FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND CODE=? GROUP BY POINTID) TT JOIN TB_POINT TP ON TT.POINTID=TP.ID GROUP BY TP.FILIALE) TTP JOIN TB_ORG TBO ON TTP.FILIALE=TBO.ID) RR ON EE.NAME=RR.NAME WHERE EE.PARENT=?";
	
	/**
	 *电视日销量战报
	 */
	public static final String SALESREC_DAYQUERY_BY_FILIALE = "SELECT NVL(SUM(TS.NUM),0),TT.NAME FROM (SELECT TP.ID,TR.NAME FROM TB_POINT TP JOIN TB_ORG TR ON TP.FILIALE = TR.ID) TT LEFT JOIN (SELECT SUM(SALESNUM) AS NUM,POINTID FROM TB_SALESREC WHERE SALESDATE=? GROUP BY POINTID) TS ON TT.ID = TS.POINTID GROUP BY TT.NAME";
	public static final String SALESREC_MONQUERY_BY_FILIALE = "SELECT CU.NAME,CU.CURR,BE.BEF FROM (SELECT NVL(SUM(TS.NUM),0) AS CURR,TT.NAME AS NAME FROM (SELECT TP.ID,TR.NAME FROM TB_POINT TP JOIN TB_ORG TR ON TP.FILIALE = TR.ID) TT LEFT JOIN (SELECT SUM(SALESNUM) AS NUM,POINTID FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? GROUP BY POINTID) TS ON TT.ID = TS.POINTID GROUP BY TT.NAME) CU JOIN (SELECT NVL(SUM(TS.NUM),0) AS BEF,TT.NAME AS NAME FROM (SELECT TP.ID,TR.NAME FROM TB_POINT TP JOIN TB_ORG TR ON TP.FILIALE = TR.ID) TT LEFT JOIN (SELECT SUM(SALESNUM) AS NUM,POINTID FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? GROUP BY POINTID) TS ON TT.ID = TS.POINTID GROUP BY TT.NAME) BE ON CU.NAME=BE.NAME";
	public static final String SALES_STATIS_FILIALE = "SELECT TP.FILIALE, TG.NAME, NVL(sum(RETAILNUM),0), NVL(sum(RETAILAMT),0), NVL(SUM(BATCHNUM),0), NVL(SUM(BATCHAMT),0), NVL(sum(ENGINNUM),0), NVL(SUM(ENGINAMT),0), NVL(SUM(WHOLENUM),0), NVL(SUM(WHOLEAMT),0) FROM (SELECT ID, FILIALE FROM TB_POINT) TP LEFT JOIN (SELECT * FROM TB_POINTDAILY WHERE SALESDATE=?) TPD ON TP.ID = TPD.POINTID LEFT JOIN TB_ORG TG ON TG.ID = TP.FILIALE GROUP BY TP.FILIALE, TG.NAME";
	public static final String SALES_STATIS_FILIALE_MON = "SELECT TP.FILIALE, TG.NAME, NVL(sum(RETAILNUM),0), NVL(sum(RETAILAMT),0), NVL(SUM(BATCHNUM),0), NVL(SUM(BATCHAMT),0), NVL(sum(ENGINNUM),0), NVL(SUM(ENGINAMT),0), NVL(SUM(WHOLENUM),0), NVL(SUM(WHOLEAMT),0) FROM (SELECT ID, FILIALE FROM TB_POINT) TP LEFT JOIN (SELECT * FROM TB_POINTDAILY WHERE SALESDATE BETWEEN ? AND ?) TPD ON TP.ID = TPD.POINTID LEFT JOIN TB_ORG TG ON TG.ID = TP.FILIALE GROUP BY TP.FILIALE, TG.NAME";
	public static final String TV_QUERY_DAILY_DETAIL="SELECT * FROM (SELECT * FROM TB_PRODUCT WHERE ISTV=2) TP LEFT JOIN (SELECT CODE,SUM(DECODE(SALESTYPE,1,SALESNUM)) AS R,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS B,SUM(DECODE(SALESTYPE,3,SALESNUM)) AS E, SUM(SALESNUM) AS SUM, MAX(UNITPRICE) AS UNITPRICE FROM TB_SALESREC WHERE SALESDATE=? GROUP BY CODE ORDER BY SUM DESC) TS ON TS.CODE =TP.CODE";
	public static final String TV_QUERY_MONTHLY_DETAIL= "SELECT * FROM (SELECT * FROM TB_PRODUCT WHERE ISTV=2) TP LEFT JOIN (SELECT CODE,SUM(DECODE(SALESTYPE,1,SALESNUM)) AS R,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS B,SUM(DECODE(SALESTYPE,3,SALESNUM)) AS E, SUM(SALESNUM) AS SUM, MAX(UNITPRICE) AS UNITPRICE FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? GROUP BY CODE ORDER BY SUM DESC) TS ON TS.CODE =TP.CODE";
	public static final String SALESREC_QUERY_DAY_ALL = "SELECT SUM(SALESNUM),SUM(SALESNUM*UNITPRICE) FROM TB_SALESREC WHERE SALESDATE=?";
	public static final String SALESREC_QUERY_DAY_SPECIAL = "SELECT SUM(SALESNUM),SUM(SALESNUM*UNITPRICE) FROM TB_SALESREC WHERE SALESDATE=? AND POINTID IN(SELECT ID FROM TB_POINT WHERE FILIALE IN (SELECT ID FROM TB_ORG WHERE NAME=?))";
	public static final String SALESREC_QUERY_MONTH_ALL = "SELECT SUM(SALESNUM),SUM(SALESNUM*UNITPRICE) FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ?";
	public static final String SALESREC_QUERY_MONTH_SPECIAL = "SELECT SUM(SALESNUM),SUM(SALESNUM*UNITPRICE) FROM TB_SALESREC WHERE SALESDATE BETWEEN ? AND ? AND POINTID IN(SELECT ID FROM TB_POINT WHERE FILIALE IN (SELECT ID FROM TB_ORG WHERE NAME=?))";
	/**
	 *电视销量对比
	 */
	public static final String POINTDAILY_QUERY_DAY = "SELECT * FROM (SELECT TT.NAME,SUM(TTT.RETAILNUM),SUM(TTT.BATCHNUM),SUM(ENGINNUM),SUM(WHOLENUM),SUM(TTT.RETAILAMT),SUM(TTT.BATCHAMT),SUM(TTT.ENGINAMT),SUM(WHOLEAMT) FROM (SELECT TP.ID,TR.NAME FROM TB_POINT TP JOIN TB_ORG TR ON TP.FILIALE = TR.ID) TT LEFT JOIN (SELECT POINTID,RETAILNUM,BATCHNUM,ENGINNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,WHOLEAMT FROM TB_POINTDAILY WHERE SALESDATE=?) TTT ON TT.ID= TTT.POINTID GROUP BY TT.NAME) XX JOIN (SELECT TT.NAME,SUM(TTT.RETAILNUM),SUM(TTT.BATCHNUM),SUM(ENGINNUM),SUM(WHOLENUM),SUM(TTT.RETAILAMT),SUM(TTT.BATCHAMT),SUM(TTT.ENGINAMT),SUM(WHOLENUM) FROM (SELECT TP.ID,TR.NAME FROM TB_POINT TP JOIN TB_ORG TR ON TP.FILIALE = TR.ID) TT LEFT JOIN (SELECT POINTID,RETAILNUM,BATCHNUM,ENGINNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,WHOLEAMT FROM TB_POINTDAILY WHERE SALESDATE=?) TTT ON TT.ID= TTT.POINTID GROUP BY TT.NAME) OO ON XX.NAME=OO.NAME";
	public static final String POINTDAILY_QUERY_DAY_ALL = "SELECT SUM(RETAILNUM),SUM(BATCHNUM),SUM(ENGINNUM),SUM(WHOLENUM),SUM(RETAILAMT),SUM(BATCHAMT),SUM(ENGINAMT),SUM(WHOLEAMT) FROM TB_POINTDAILY WHERE SALESDATE=?";
	public static final String POINTDAILY_QUERY_DAY_ZONE="SELECT * FROM (SELECT TT.NAME,SUM(TTT.RETAILNUM),SUM(TTT.BATCHNUM),SUM(ENGINNUM),SUM(WHOLENUM),SUM(TTT.RETAILAMT),SUM(TTT.BATCHAMT),SUM(TTT.ENGINAMT),SUM(WHOLEAMT) FROM (SELECT TP.ID,TR.NAME FROM TB_POINT TP JOIN TB_ORG TR ON TP.ZONE1 = TR.ID) TT LEFT JOIN (SELECT POINTID,RETAILNUM,BATCHNUM,ENGINNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,WHOLEAMT FROM TB_POINTDAILY WHERE SALESDATE=?) TTT ON TT.ID= TTT.POINTID GROUP BY TT.NAME) XX JOIN (SELECT TT.NAME,SUM(TTT.RETAILNUM),SUM(TTT.BATCHNUM),SUM(ENGINNUM),SUM(WHOLENUM),SUM(TTT.RETAILAMT),SUM(TTT.BATCHAMT),SUM(TTT.ENGINAMT),SUM(WHOLEAMT) FROM (SELECT TP.ID,TR.NAME FROM TB_POINT TP JOIN TB_ORG TR ON TP.ZONE1 = TR.ID) TT LEFT JOIN (SELECT POINTID,RETAILNUM,BATCHNUM,ENGINNUM,WHOLENUM,RETAILAMT,BATCHAMT,ENGINAMT,WHOLEAMT FROM TB_POINTDAILY WHERE SALESDATE=?) TTT ON TT.ID= TTT.POINTID GROUP BY TT.NAME) OO ON XX.NAME=OO.NAME";
	
	/**
	 *竞品管理
	 */
	public static final String COMPETITION_INS = "INSERT INTO TB_COMPETITION(ID,BRAND,CODE,ISTV,CLASS1,CLASS2,CLASS3,CLASS4,SPEC,ISMAIN,MKTDATE,REGIDATE,REFPRICE,ISCOUNTRY,POPUTYPE,TYPE1,TYPE2,TYPE3,TYPE4,STATE,PROMOTOR,FILIALEID,ORGID,PROMOTORNAME,FILIALENAME,ORGNAME) VALUES(SEQ_COMPETITION.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String COMPETITION_UPDATE_BY_ID = "UPDATE TB_COMPETITION SET CODE=?,CLASS1=?,CLASS2=?,SPEC=?,ISMAIN=?,REFPRICE=?,ISCOUNTRY=?,STATE=? WHERE ID=?";
	public static final String COMPETITION_DEL_BY_ID = "DELETE FROM TB_COMPETITION WHERE ID=?";
	public static final String COMPETITION_QUERY_BY_BRAND = "SELECT ID,BRAND,CODE FROM TB_COMPETITION WHERE BRAND=?";
	public static final String COMPETITION_UPDATE_STATE="UPDATE TB_COMPETITION SET STATE =? WHERE ID=?";
	public static final String COMPETITION_QUERY_STATE_BY_ID="SELECT STATE FROM TB_COMPETITION WHERE ID=?";
	public static final String COMPETITION_QUERY_BY_ID="SELECT * FROM TB_COMPETITION WHERE ID=?";
	public static final String COMPETITION_QUERY_BRAND_CODE="SELECT ID FROM TB_COMPETITION WHERE BRAND=? AND CODE=?";
	public static final String COMPETITION_UPDATE_STATE_UNPASS = "UPDATE TB_COMPETITION SET STATE = 3 WHERE ID=?";
	public static final String COMPETTERMINAL_INS="INSERT INTO TB_COMPETTERMINAL VALUES(SEQ_COMPETTERMINAL.nextval,?,?,?)";
	public static final String COMPETTERMINAL_UPD = "UPDATE TB_COMPETTERMINAL SET RANK=? WHERE FILIALE=? AND BRAND=?";
	public static final String COMPETTERMINAL_QUERY="SELECT ID FROM TB_COMPETTERMINAL WHERE FILIALE =? AND BRAND=?";
	public static final String COMPETTERMINAL_QUERY_ALL="SELECT * FROM TB_COMPETTERMINAL";
	public static final String COMPETTERMINAL_QUERY_BY_FILIALE = "SELECT BRAND FROM TB_COMPETTERMINAL WHERE FILIALE=? AND RANK<=?";
	public static final String COMPETTERMINAL_EXPORT="SELECT BRAND,CODE,CLASS1,CLASS2,CLASS3,CLASS4,SPEC,ISMAIN,REFPRICE,MKTDATE,TYPE1,TYPE2,TYPE3,TYPE4,SIZES FROM TB_COMPETITION";
	/**
	 *竞品 上报
	 */
	
	public static final String COMPETITIONSALES_INS = "INSERT INTO TB_COMPETITIONSALES(ID,COMPEID,POINTID,STANDTYPE,PROMOTOR,BRAND,CODE,SALESNUM,UNITPRICE,SALESTYPE,MEMO,SALESDATE,REPORTTIME,TERMINAL,NIAN,YUE,SOURCE) VALUES(SEQ_COMPETITIONSALES.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String COMPETITIONSALES_INS2 = "INSERT INTO TB_COMPETITIONSALES(ID,COMPEID,BRAND,CATEGORY,CODE,SALESNUM,UNITPRICE,SALESTYPE,OPERATE,STANDTYPE,MEMO,SALESDATE,REPORTTIME,POINTID,SOURCE) VALUES(SEQ_SALESREC.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	   

	public static final String COMPETITIONSALES_IMPORT = "INSERT INTO TB_COMPETITIONSALES(ID,COMPEID,POINTID,STANDTYPE,PROMOTOR,BRAND,CODE,SALESNUM,UNITPRICE,SALESTYPE,MEMO,SALESDATE,REPORTTIME,TERMINAL,NIAN,YUE,SOURCE,PROMOTORNAME) VALUES(SEQ_COMPETITIONSALES.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	public static final String COMPETITIONSALES_UPDATE_BY_ID="UPDATE TB_COMPETITIONSALES SET BRAND=?,CODE=?,SALESNUM=?,UNITPRICE=?,SALESTYPE=?,MEMO=? WHERE ID=?";
	
	public static final String COMPETITIONSALES_QUERY_BY_POINTID="SELECT SUM(DECODE(SALESTYPE,1,SALESNUM)) AS NUM1,SUM(DECODE(SALESTYPE,2,SALESNUM)) AS NUM2,SUM(DECODE(SALESTYPE,3,SALESNUM))AS NUM3,SUM(SALESNUM)AS NUM4,SUM(DECODE(SALESTYPE,1,SALESNUM*UNITPRICE))AS NUM5,SUM(DECODE(SALESTYPE,2,SALESNUM*UNITPRICE)) AS NUM6,SUM(DECODE(SALESTYPE,3,SALESNUM*UNITPRICE))AS NUM7,SUM(SALESNUM*UNITPRICE) AS NUM8  FROM TB_COMPETITIONSALES WHERE SALESDATE BETWEEN ? AND ? ";// 
	public static final String COMPETITIONSALES_QUERY_ALL ="SELECT SUM(SALESNUM),SUM(SALESNUM*UNITPRICE) FROM TB_COMPETITIONSALES WHERE POINTID=? AND SALESDATE BETWEEN ? AND ?";
	public static final String COMPETITIONSALES_QUERY_ALL_1 = "SELECT BRAND,SUM(SALESNUM) AS NUM,SUM(SALESNUM*UNITPRICE) AS AMT FROM TB_COMPETITIONSALES  WHERE POINTID=? AND SALESDATE BETWEEN ? AND ? GROUP BY BRAND";
	public static final String COMPETITIONSALES_QUERY_DETAIL="SELECT MAX(BRAND),CODE,SUM(SALESNUM),SUM(UNITPRICE*SALESNUM) FROM TB_COMPETITIONSALES WHERE SALESDATE BETWEEN ? AND ? ";
	public static final String COMPETITIONSALES_TOTAL_WEEK= "SELECT * FROM (SELECT BRAND, SUM(SALESNUM) AS ALLNUM FROM TB_COMPETITIONSALES WHERE SALESDATE<=? GROUP BY BRAND) TA JOIN (SELECT BRAND, SUM(SALESNUM) AS WNUM FROM TB_COMPETITIONSALES WHERE SALESDATE BETWEEN ? AND ? GROUP BY BRAND) TW ON TA.BRAND=TW.BRAND";
	//明细查询 add by stanlyy 20130831
	public static final String COMPETITIONSALES_QUERY_DETAIL2="SELECT MAX(BRAND),MAX(CATEGORY),CODE,SUM(SALESNUM),SUM(UNITPRICE*SALESNUM) FROM TB_COMPETITIONSALES WHERE SALESDATE BETWEEN ? AND ? ";

	/**
	 *自定义任务
	 */
	public static final String DEFINETASK_INS = "INSERT INTO TB_DEFINETASK(ID,TASKTYPE,TASKNAME) VALUES(SEQ_DEFINETASK.nextval,?,?)";
	public static final String DEFINETASK_UPD_BY_ID = "UPDATE TB_DEFINETASK SET TASKTYPE=?,TASKNAME=? WHERE ID=?";
	public static final String DEFINETASK_DEL_BY_ID="DELETE FROM TB_DEFINETASK WHERE ID=?";
	public static final String DEFINETASK_QUERY_ALL = "SELECT * FROM TB_DEFINETASK";
	/**
	 * 销量任务
	 */
	public static final String SALESTASK_INS = "INSERT INTO TB_SALESTASK(ID,GOALID,TIMENAME,STARTDATE,ENDDATE,NUM,AMOUNT,SENDTIME,TASKTYPE,LEVELS,BRAND,CATEGORY,MODIFYDATE,MEMO,ISSUEDORG,YEARMONTH) " +
			"VALUES(SEQ_SALESTASK.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String POINT_QUERY_ALL_ID = "SELECT ID FROM TB_POINT WHERE FILIALE=? OR AGENCY=? OR AREA=? OR ID=?";
	public static final String SALESTASK_UPD_BY_ID ="UPDATE TB_SALESTASK SET NUM=?,AMOUNT=? WHERE ID=?";
	public static final String SALESTASK_DEL_BY_ID = "DELETE FROM TB_SALESTASK WHERE ID=?";
	//竞品查询 add by stanlyy 20130831
	public static final String POINT_QUERY_ALL_ID2 = "SELECT ID FROM TB_POINT WHERE  FILIALE=? OR AGENCY=? OR AREA=? OR ID=?";
 	// 以下系列语句用于建立数据库对象
	public static final String[] BUILD_DATABASE_OBJECT = {
		"",
		"",
		"",
		"",
		"",
		"",
		"",
		"",
	}; 
	
	/**
	 * 进行调节查询时使用
	 * @param sql
	 * @return
	 */
	public static String getPageSQL(String sql) {
		// return "SELECT * FROM(" + sql +
		// ") WHERE ROWNUM<=? MINUS SELECT * FROM(" + sql + ") WHERE ROWNUM<?";
		return "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (" + sql + ") A WHERE ROWNUM <= ?)WHERE RN >= ?";
	}
	
	
//	======================================================================================================
//    													冰
//  ======================================================================================================
//														洗
//	======================================================================================================

	/**
	*产品管理
	*/
	//添加
	public static final String ICEBOX_INS = "INSERT INTO TB_ICEBOX(ID,CATEGORY,CODE,NAME,MATERIALCODE,TYPENUM,CLASS1,CLASS2,CLASS3,CLASS4,SPEC,ISMAIN,ISCOUNTRY,GRADE,CONTROLWAY,ENERGYLEVEL,ISGRACE,SETS,DOOR,WOLUMES,ENERGYSAVE,MKTDATE,REGIDATE,STATE,MEMO,COSTPRICE,SUPPPRICE,PRICEH,PRICEL,BRAND,PRICENOTAX,ISDISPLAY) VALUES(SEQ_ICEBOX.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String WASHER_INS = "INSERT INTO TB_WASHER(ID,CATEGORY,CODE,NAME,MATERIALCODE,TYPENUM,CLASS1,CLASS2,CLASS3,CLASS4,SPEC,ISMAIN,ISCOUNTRY,GRADE,CONTROLWAY,ENERGYLEVEL,ISGRACE,SETS,WOLUME,SERIES,CHANNEL,STRUCTURE,ENERGYSAVE,MKTDATE,REGIDATE,STATE,MEMO,COSTPRICE,SUPPPRICE,PRICEH,PRICEL,BRAND,PRICENOTAX,ISDISPLAY) VALUES(SEQ_WASHER.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String COOLER_INS = "INSERT INTO TB_COOLER(ID,CATEGORY,CODE,NAME,MATERIALCODE,TYPENUM,CLASS1,CLASS2,CLASS3,CLASS4,SPEC,ISMAIN,ISCOUNTRY,GRADE,CONTROLWAY,ENERGYLEVEL,ISGRACE,SETS,DOOR,WOLUMES,FUNCTIONS,SIZES,ENERGYSAVE,MKTDATE,REGIDATE,STATE,MEMO,COSTPRICE,SUPPPRICE,PRICEH,PRICEL,BRAND,PRICENOTAX,ISDISPLAY) VALUES(SEQ_COOLER.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String AC_INS = "INSERT INTO TB_AC(ID,BRAND,CATEGORY,CODE,NAME,MATERIALCODE,TYPENUM,CLASS1,CLASS2,SPEC,ISMAIN,ISCOUNTRY,GRADE,CONTROLWAY,ENERGYLEVEL,ISGRACE,COSTPRICE,SUPPPRICE,CLEANPRICE1,CLEANPRICE2,STATE,ENERGYSAVE,ACSERIAL,ACFUNC,ISFC,ACTYPE,HPOWER,CPOWER,ISDC,MKTDATE,REGIDATE,MEMO,TYPE,PROAGE,PRICEH,PRICEL,SETS,PRICENOTAX,ISDISPLAY) VALUES(SEQ_AC.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	//添加成本价
	public static final String PRODUCT_COSTPRICE_INS = "INSERT INTO TB_PRODUCT_PRICE1(ID, CATEGORY, CODE, COST, RECORDTIME,BRAND) VALUES (SEQ_PRODUCT_PRICE1.nextval,?,?,?,?,?)";
	public static final String PRODUCT_COSTPRICE_UPD = "UPDATE TB_PRODUCT_PRICE1 SET COST=?,RECORDTIME=? WHERE CODE=? AND RECORDTIME BETWEEN ? AND ?";
	//添加供价
	public static final String PRODUCT_SUPPPRICE_INS = "INSERT INTO TB_PRODUCT_PRICE2(ID, CATEGORY, CODE, SUPPPRICE, RECORDTIME,BRAND) VALUES (SEQ_PRODUCT_PRICE2.nextval,?,?,?,?,?)";
	public static final String PRODUCT_SUPPRICE_UPD  = "UPDATE TB_PRODUCT_PRICE2 SET SUPPPRICE=?,RECORDTIME=? WHERE CODE=? AND RECORDTIME BETWEEN ? AND ?";
	
	//添加扣政策净价
	public static final String PRODUCT_NOTAXPRICE_INS = "INSERT INTO TB_PRODUCT_PRICE3(ID, CATEGORY, CODE, NOTAX, RECORDTIME,BRAND) VALUES (SEQ_PRODUCT_PRICE3.nextval,?,?,?,?,?)";
	public static final String PRODUCT_NOTAXPRICE_UPD  = "UPDATE TB_PRODUCT_PRICE3 SET NOTAX=?,RECORDTIME=? WHERE CODE=? AND RECORDTIME BETWEEN ? AND ?";
	
	//删除
	public static final String ICEBOX_DEL_BY_ID = "DELETE FROM TB_ICEBOX WHERE ID=?";
	public static final String WASHER_DEL_BY_ID = "DELETE FROM TB_WASHER WHERE ID=?";
	public static final String COOLER_DEL_BY_ID = "DELETE FROM TB_COOLER WHERE ID=?";
	//修改
	public static final String ICEBOX_UPD_BY_ID = "UPDATE TB_ICEBOX SET ISDISPLAY=?,CATEGORY=?,CODE=?,NAME=?,MATERIALCODE=?,TYPENUM=?,CLASS1=?,CLASS2=?,CLASS3=?,CLASS4=?,SPEC=?,ISMAIN=?,ISCOUNTRY=?,GRADE=?,CONTROLWAY=?,ENERGYLEVEL=?,ISGRACE=?,SETS=?,DOOR=?,WOLUMES=?,ENERGYSAVE=?,MKTDATE=?,REGIDATE=?,STATE=?,MEMO=?,COSTPRICE=?,SUPPPRICE=?,PRICEH=?,PRICEL=?,BRAND = ?,PRICENOTAX= ? WHERE ID=?";
	public static final String WASHER_UPD_BY_ID = "UPDATE TB_WASHER SET ISDISPLAY=?,CATEGORY=?,CODE=?,NAME=?,MATERIALCODE=?,TYPENUM=?,CLASS1=?,CLASS2=?,CLASS3=?,CLASS4=?,SPEC=?,ISMAIN=?,ISCOUNTRY=?,GRADE=?,CONTROLWAY=?,ENERGYLEVEL=?,ISGRACE=?,SETS=?,WOLUME=?,SERIES=?,CHANNEL=?,STRUCTURE=?,ENERGYSAVE=?,MKTDATE=?,REGIDATE=?,STATE=?,MEMO=?,COSTPRICE=?,SUPPPRICE=?,PRICEH=?,PRICEL=?,BRAND = ? ,PRICENOTAX= ? WHERE ID=?";
	public static final String COOLER_UPD_BY_ID = "UPDATE TB_COOLER SET ISDISPLAY=?,CATEGORY=?,CODE=?,NAME=?,MATERIALCODE=?,TYPENUM=?,CLASS1=?,CLASS2=?,CLASS3=?,CLASS4=?,SPEC=?,ISMAIN=?,ISCOUNTRY=?,GRADE=?,CONTROLWAY=?,ENERGYLEVEL=?,ISGRACE=?,SETS=?,DOOR=?,WOLUMES=?,FUNCTIONS=?,SIZES=?,ENERGYSAVE=?,MKTDATE=?,REGIDATE=?,STATE=?,MEMO=?,COSTPRICE=?,SUPPPRICE=?,PRICEH=?,PRICEL=?,BRAND = ? ,PRICENOTAX= ? WHERE ID=?";
	public static final String AC_UPD_BY_ID = "UPDATE TB_AC SET BRAND=?,CATEGORY=?,CODE=?,NAME=?,MATERIALCODE=?,TYPENUM=?,CLASS1=?,CLASS2=?,SPEC=?,ISMAIN=?,ISCOUNTRY=?,GRADE=?,CONTROLWAY=?,ENERGYLEVEL=?,ISGRACE=?,COSTPRICE=?,SUPPPRICE=?,CLEANPRICE1=?,CLEANPRICE2=?,STATE=?,ENERGYSAVE=?,ACSERIAL=?,ACFUNC=?,ISFC=?,ACTYPE=?,HPOWER=?,CPOWER=?,ISDC=?,MKTDATE=?,REGIDATE=?,MEMO=?,TYPE=?,PROAGE=?,PRICEH=?,PRICEL=?,SETS=?,PRICENOTAX=?,ISDISPLAY=? WHERE ID=?";
	
	//根据ID查询
	public static final String ICEBOX_QUERY_BY_ID = "SELECT * FROM TB_ICEBOX WHERE ID=?";
	public static final String WASHER_QUERY_BY_ID = "SELECT * FROM TB_WASHER WHERE ID=?";
	public static final String COOLER_QUERY_BY_ID = "SELECT * FROM TB_COOLER WHERE ID=?";
	public static final String AC_QUERY_BY_ID = "SELECT * FROM TB_AC WHERE ID=? ";
	//查询所有code 
	public static final String ICEBOX_QUERY_CODE = "SELECT ID, CATEGORY, CODE, COSTPRICE, SUPPPRICE, PRICEH, PRICEL, STATE FROM TB_ICEBOX ";
	public static final String WASHER_QUERY_CODE = "SELECT ID, CATEGORY, CODE, COSTPRICE, SUPPPRICE, PRICEH, PRICEL, STATE FROM TB_WASHER ";
	public static final String COOLER_QUERY_CODE = "SELECT ID, CATEGORY, CODE, COSTPRICE, SUPPPRICE, PRICEH, PRICEL, STATE FROM TB_COOLER ";
	public static final String AC_QUERY_CODE = "SELECT ID, CATEGORY, CODE, COSTPRICE, SUPPPRICE, PRICEH, PRICEL, STATE FROM TB_AC ";
	public static final String COMPETEAC_QUERY_CODES = "SELECT ID, CATEGORY, CODE, COSTPRICE, SUPPPRICE, PRICEH, PRICEL, STATE FROM TB_COMPETEAC ";
	
   /**
	*竞品管理 
	*/
	//冰箱 
	public static  String COMPETEICEBOX  = "select id,category,brand,code,productname,materialcode,typenum,class1,class2,class3,class4,spec,ismain,iscountryside,grade,controlway," +
			                               "energylevel,isgrace,type,sets,validitystate,door,wolumes,energysave,costprice,netprice,deduction1,deduction2,priceh,pricel,"+
			                               "mktdate,regidate,attach,auditingstate,promotorid,promotorname,orgid,orgname,filialeid,filialename from TB_COMPETE_ICEBOX";
	//洗衣机
	public static  String COMPETEWASHER  = "select id,category,brand,code,productname,materialcode,typenum,class1,class2,class3,class4,spec,ismain,iscountryside,grade,controlway,"+
	                                       "energylevel,isgrace,type,sets,validitystate,wolume,series,channel,structure,costprice,netprice,deduction1,deduction2,priceh,pricel,"+
	                                       "mktdate,regidate,attach,auditingstate,promotorid,promotorname,orgid,orgname,filialeid,filialename from TB_COMPETE_WASHER";
	//冷柜
	public static  String COMPETECOOLER = "select id,category,brand,code,productname,materialcode,typenum,class1,class2,class3,class4,spec,ismain,"+
	                                       "iscountryside,grade,controlway,energylevel,isgrace,type,sets,validitystate,door,wolumes,functions,"+
	                                       "sizenum,costprice,netprice,deduction1,deduction2,priceh,pricel,mktdate,regidate,attach,auditingstate,"+
	                                       "promotorid,promotorname,orgid,orgname,filialeid,filialename from TB_COMPETE_COOLER ";
	
	//竞品空调
	public static String COMPETEAC = "select id,brand,category,code,name,materialcode,typenum,class1,class2,spec,ismain,iscountry,grade,controlway,energylevel,isgrace,"+
									" costprice,suppprice,cleanprice1,cleanprice2,state,energysave,acserial,acfunc,isfc,actype,hpower,cpower,isdc,mktdate,regidate,"+
									" memo,type,proage,pricel,promotorid,auditingstate,priceh,orgid,orgname,promotorname,filialeid,filialename,sets,refprice,pricenotax  "+
									" from TB_COMPETEAC";
	
	
	//竞品冰箱 
	public static final String COMPETEICEBOX_QUERY_CODE = "SELECT ID,BRAND,CATEGORY,CODE FROM TB_COMPETE_ICEBOX";
	//竞品洗衣机
	public static final String COMPETEWASHER_QUERY_CODE = "SELECT ID,BRAND,CATEGORY,CODE FROM TB_COMPETE_WASHER";
	//竞品冷柜
	public static final String COMPETECOOLER_QUERY_CODE = "SELECT ID,BRAND,CATEGORY,CODE FROM TB_COMPETE_COOLER";
	
	//竞品空调
	public static final String COMPETEAC_QUERY_CODE = "SELECT ID,BRAND,CATEGORY,CODE FROM TB_COMPETEAC";
	//竞品管理---插入空调 
	public static final String COMPETEACINSERT = "insert into tb_competeac("+
								" id,brand,category,code,name,materialcode,typenum,class1,class2,spec,ismain,iscountry,grade,controlway,energylevel,isgrace,"+
								" costprice,suppprice,cleanprice1,cleanprice2,state,energysave,acserial,acfunc,isfc,actype,hpower,cpower,isdc,mktdate,regidate,"+
								" memo,type,proage,pricel,promotorid,auditingstate,priceh,orgid,orgname,promotorname,filialeid,filialename,sets,refprice,pricenotax ) "+
								" values(seq_competeac.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
	
	//竞品管理---更新空调
	public static final String COMPETEAC_UPDATE = "update tb_competeac set "+
								" code=?,name=?,materialcode=?,typenum=?,class1=?,class2=?,spec=?,ismain=?,iscountry=?,grade=?,controlway=?,energylevel=?,isgrace=?,"+
								" energysave=?,acserial=?,acfunc=?,isfc=?,actype=?,hpower=?,cpower=?,isdc=?,mkdate=?,type=?,proage=?,pricel=?,priceh=?,sets=? "+
								" where id = ? ";
	
	//竞品管理---插入冰箱
	public  static final String ICEBOXINSERT = "insert into tb_compete_icebox(" +
					            "id,category,brand,code,productname,materialcode,typenum,class1,class2,class3,class4,spec,ismain,iscountryside,grade,controlway," +
					            "energylevel,isgrace,type,sets,validitystate,door,wolumes,energysave,pricel,"+
					            "mktdate,regidate,auditingstate,promotorid,promotorname,orgid,orgname,filialeid,filialename	) " +
					            "values(seq_compete_icebox.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	//竞品管理---插入洗衣机
	public  static final String WASHERINSERT = "insert into tb_compete_washer(" +
			                    "id,category,brand,code,productname,materialcode,typenum,class1,class2,class3,class4,spec,ismain,iscountryside,grade,controlway,"+
	                            "energylevel,isgrace,type,sets,validitystate,wolume,series,channel,structure,pricel,"+
	                            "mktdate,regidate,auditingstate,promotorid,promotorname,orgid,orgname,filialeid,filialename) " +
	                            "values(seq_compete_washer.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	//竞品管理---插入冷柜
	public  static final String COOLERINSERT = "insert into tb_compete_cooler(" +
			                    "id,category,brand,code,productname,materialcode,typenum,class1,class2,class3,class4,spec,ismain,"+
	                            "iscountryside,grade,controlway,energylevel,isgrace,type,sets,validitystate,door,wolumes,functions,"+
	                            "sizenum,pricel,mktdate,regidate,auditingstate,promotorid,promotorname,orgid,orgname,filialeid,filialename) " +
	                            "values(seq_compete_cooler.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	//竞品管理---更新冰箱 
	public static final String COMPETE_UPDATE_ICEBOX ="UPDATE TB_COMPETE_ICEBOX SET CODE=?,PRODUCTNAME=?,MATERIALCODE=?,TYPENUM=?, CLASS1=?,CLASS2=?,CLASS3=?,CLASS4=?,SPEC=?,ISMAIN=?,ISCOUNTRYSIDE=?,GRADE=?,CONTROLWAY=?," +
			                                          "ENERGYLEVEL=?,ISGRACE=?,TYPE=?,SETS=?,DOOR=?,WOLUMES=?,ENERGYSAVE=?,PRICEL=?,MKTDATE=? WHERE ID=?";
	//竞品管理---更新冷柜
	public static final String COMPETE_UPDATE_COOLER ="UPDATE TB_COMPETE_COOLER SET CODE=?,PRODUCTNAME=?,MATERIALCODE=?,TYPENUM=?,CLASS1=?,CLASS2=?,CLASS3=?,CLASS4=?,SPEC=?,ISMAIN=?,ISCOUNTRYSIDE=?,GRADE=?,CONTROLWAY=?,"+
	                                                  "ENERGYLEVEL=?,ISGRACE=?,TYPE=?,SETS=?,DOOR=?,WOLUMES=?,FUNCTIONS=?,SIZENUM=?,PRICEL=?,MKTDATE=? WHERE ID=?";
	//竞品管理---更新洗衣机
	public static final String COMPETE_UPDATE_WASHER="UPDATE TB_COMPETE_WASHER  SET CODE=?,PRODUCTNAME=?,MATERIALCODE=?,TYPENUM=?,CLASS1=?,CLASS2=?,CLASS3=?,CLASS4=?,SPEC=?,ISMAIN=?,ISCOUNTRYSIDE=?,GRADE=?,CONTROLWAY=?,"+
                                                      "ENERGYLEVEL=?,ISGRACE=?,TYPE=?,SETS=?,WOLUMES=?,SERIES=?,CHANNEL=?,STRUCTURE=?,PRICEL=?,MKTDATE=? WHERE ID=?"; 
	
	//销售管理---竞品销售上报
	public static final String New_COMPETITIONSALES_INS= "INSERT INTO TB_COMPETITIONSALES(ID,COMPEID,BRAND,CATEGORY,POINTID,STANDTYPE,PROMOTOR,CODE,SALESNUM,UNITPRICE,SALESTYPE,MEMO,SALESDATE,REPORTTIME,TERMINAL,NIAN,YUE,SOURCE,PROMOTORNAME,OPERATE) VALUES(SEQ_SALESREC.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	//竞品销售记录查询
	public static final String CompetitionSales_QUERY_SaleDetail_ID = "SELECT * FROM tb_competitionsales WHERE ID=?";
   
	//竞品销售记录更新
	public static final String COMPETITIONSALES_UPDATE_BYWEB_ID = "UPDATE TB_COMPETITIONSALES SET SALESNUM=?,UNITPRICE=?,SALESTYPE=?,MEMO=?,operate=? WHERE ID=?";
	
	//竞品销量查询---导出今日明细
	public static final String COMPETITIONSALES_CURR_DETAIL = "SELECT TBO.NAME AS NAME, TBC.TERMINAL AS TERMINAL, TBC.BRAND AS BRAND, TBC.CODE AS CODE, TBC.UNITPRICE AS UNITPRICE, TBC.SALESNUM AS SALESNUM, TBC.SALESTYPE AS SALESTYPE, TBC.MEMO AS MEMO FROM (SELECT POINTID,TERMINAL,BRAND,CODE,UNITPRICE,SALESNUM,SALESTYPE,MEMO FROM TB_COMPETITIONSALES WHERE REPORTTIME > ? AND REPORTTIME <= ? AND POINTID IN (SELECT ID FROM TB_POINT WHERE FILIALE = ?)) TBC JOIN (SELECT ID, NAME FROM TB_ORG) TBO ON TBC.POINTID = TBO.ID";
	
	//竞品销售记录---删除
	public static final String COMPETITIONSALES_DEL_BY_ID="DELETE FROM TB_COMPETITIONSALES WHERE ID=?";
	//竞品销售记录---更新
	public static final String CompetitionSales_UPD_BY_ID = "UPDATE tb_competitionsales SET SALESTYPE=?,OPERATE=?,STANDTYPE=?,MEMO=?,SALESNUM=?,UNITPRICE=? WHERE ID=?";
    //竞品销量记录---未上报分公司
	//public static final String COMPETITIONSALES_QUERY_UN="SELECT POINTID ,SUM(SALESNUM) AS SALESNUM FROM TB_COMPETITIONSALES WHERE SALESDATE BETWEEN ? AND ? GROUP BY POINTID";
	public static final String COMPETITIONSALES_QUERY_UN="SELECT POINTID  FROM TB_COMPETITIONSALES WHERE SALESDATE BETWEEN ? AND ? ";
	
	/**
	 * 电子台账
	 * */
	//插入电子台账
	public static final String CLIENT_INSERT ="INSERT INTO TB_CLIENTSALESINFO (ID,CLIENTNAME,SALEDATE,SALENUM,CONTACT,ADDRESS,ISGIFT,ORGID,USERID,CODE,BRAND,CATEGORY,REPORTTIME,ISDELIVER,CARDNO,MEMO) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	//根据id删除电子台账
	public static final String CLIENT_DELETE_BY_ID = "DELETE FROM TB_CLIENTSALESINFO WHERE ID = ? ";
	public static final String CLIENT_GIFTS_DELETE_BY_ID = "DELETE FROM TB_CLIENT_GIFTS WHERE CID = ? ";
	
	//根据id修改促销品
	public static final String CLIENT_UPDATE_BY_ID = "UPDATE TB_GIFTS SET GIFTNUM = ?,ISVALID = ?,STARTDATE = ?,ENDDATE = ?,MEMO = ? WHERE ID = ?";
	
	
	/**
	 * 产品展示
	 * */
	//插入产品展示
	public static final String PRODUCTDEMO_INSERT ="INSERT INTO TB_PRODUCTDEMO (ID,TITLE,BRAND,CATEGORY,CODE,ORGID,DOCNAME,DEMOTYPE,FILEPATH,DEMODESC,CREATEDATE,ISVALID,DOCID,UPLOADNAME,FILESIZE) VALUES (SEQ_PRODUCTDEMO.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	//根据id删除产品展示
	public static final String PRODUCTDEMO_DELETE_BY_ID = "DELETE FROM TB_PRODUCTDEMO WHERE ID = ? ";
	//根据id修改产品展示
	public static final String PRODUCTDEMO_UPDATE_BY_ID = "UPDATE TB_PRODUCTDEMO SET TITLE = ?,CODE = ?,DEMOTYPE = ?,ISVALID = ?,DEMODESC = ? WHERE ID = ?";
	
	/**
	 * 培训展示
	 * */
	//插入培训展示
	public static final String TRAIN_INSERT ="INSERT INTO TB_TRAININGDEMO (ID,TITLE,ORGID,CONTENT,MEMO,CREATEDATE,ISVALID,STARTDATE,ENDDATE) VALUES (SEQ_TRAININGDEMO.nextval,?,?,?,?,?,?,?,?)";
	//根据id删除培训展示
	public static final String TRAIN_DELETE_BY_ID = "DELETE FROM TB_TRAININGDEMO WHERE ID = ? ";
	//根据id修改培训展示
	public static final String TRAIN_UPDATE_BY_ID = "UPDATE TB_TRAININGDEMO SET TITLE = ?,CONTENT = ?,ISVALID = ?,MEMO = ?,STARTDATE = ?,ENDDATE = ? WHERE ID = ?";
	
	
	/**
	 * 活动
	 */
	public static final String HISACTIVITY_INS = "INSERT INTO TB_HISACTIVITY(ID,ACTNAME,ACTTYPE,ACTDESC,STARTTIME,ENDTIME,ACTSTATE,CREATEPERSON,CREATEORG,CREATETIME) VALUES (SEQ_HISACTIVITY.nextval,?,?,?,TO_DATE(?,'yyyy-MM-dd'),TO_DATE(?,'yyyy-MM-dd'),?,?,?,?) ";
	public static final String HISACTIVITY_QUERY_ID = "SELECT MAX(ID) FROM TB_HISACTIVITY";
	public static final String HISACTIVITY_ORG_INS = "INSERT INTO TB_HISACTIVITY_ORG(ID,ACTORGID,ACTIVITYID) VALUES (SEQ_HISACTIVITY_ORG.nextval,?,?)";
	public static final String HISACTIVITY_QUERY_IDUN = "SELECT NVL(ID,0)  FROM TB_HISACTIVITY WHERE ACTNAME=?";
	public static final String HISACTDETAIL_BY_ID="SELECT TA.ACTNAME,TA.ACTTYPE,TA.STARTTIME,TA.ENDTIME,TA.ACTSTATE,TA.ACTDESC,TOR.NAME PORGNAME,TU.NAME PPNAME,TA.ID FROM (SELECT ACTNAME,ACTTYPE,STARTTIME,ENDTIME,ACTSTATE,ACTDESC,ID,CREATEORG,CREATEPERSON FROM TB_HISACTIVITY WHERE 1=1 AND ID=? ORDER BY STARTTIME) TA  JOIN (SELECT ID,NAME FROM TB_ORG ) TOR ON TOR.ID=TA.CREATEORG JOIN (SELECT ID,NAME FROM TB_USER ) TU ON TU.ID=TA.CREATEPERSON ";
	public static final String HISACT_UPDATE_BY_ID="UPDATE TB_HISACTIVITY SET ACTTYPE=?,ACTSTATE=?,ACTDESC=?,STARTTIME=TO_DATE(?, 'yyyy-MM-dd'),ENDTIME=TO_DATE(?, 'yyyy-MM-dd') WHERE ID=?";
	public static final String HISACTFB_INS="INSERT INTO TB_HISACTIVITYFB (ID,ACTIVITYID,FBORGID,FBPERSON,FBTIME,FBCONTENT,FILEPATH,DOCNAME,DOCID,UPLOADNAME) VALUES (SEQ_HISACTIVITYFB.nextval,?,?,?,?,?,?,?,?,?)";
	public static final String COMACT_INS="INSERT INTO TB_COMPITIONACTFB (ID,BRANDID,FBORGID,FBPERSON,ACTTYPE,FBTIME,FBCONTENT,FILEPATH,DOCNAME,DOCID,UPLOADNAME,STARTTIME,ENDTIME) VALUES (SEQ_COMPITIONACTFB.nextval,?,?,?,?,TO_DATE(?,'yyyy-MM-dd'),?,?,?,?,?,TO_DATE(?,'yyyy-MM-dd'),TO_DATE(?,'yyyy-MM-dd'))";
	
	/**
	 * 库存管理
	 */
	public static final String STORAGE_INSERT ="INSERT INTO TB_STORAGE(ID,PRODID,CODE,POINTID,BRAND,CATEGORY,NUM,STORAGEDATE,STORAGEID) VALUES (SEQ_STORAGE.NEXTVAL,?,?,?,?,?,?,?,?)";
	public static final String STORAGE_MANAGE_INSERT ="INSERT INTO TB_STORAGE_MANAGE(ID,PRODID,CODE,POINTID,BRAND,CATEGORY,NUM,STORAGEDATE,STORAGEID) VALUES (SEQ_STORAGE_MANAGE.NEXTVAL,?,?,?,?,?,?,?,?)";
	public static final String STORAGE_QUERY_BY_ID = "SELECT * FROM TB_STORAGE WHERE ID=?";
	public static final String STORAGE_QUERY_BY_VO = "SELECT * FROM TB_STORAGE WHERE BRAND=? and code=? and POINTID=?";
	public static final String STORAGE_QUERYRATIO_BY_ID = "SELECT S.*,TP.SHORTNAME AS POINTNAME,TU.REALNAME AS USERNAME FROM TB_STORAGEWARN  S INNER JOIN TB_POINT TP ON TP.ID = S.POINTID LEFT JOIN TB_USER TU ON TU.ID = S.USERID WHERE S.ID=?";
	public static final String STORAGE_QUERYRATIO_BY_POINTID = "SELECT S.*,TP.SHORTNAME AS POINTNAME,TU.REALNAME AS USERNAME FROM TB_STORAGEWARN  S INNER JOIN TB_POINT TP ON TP.ID = S.POINTID LEFT JOIN TB_USER TU ON TU.ID = S.USERID WHERE S.POINTID=?";
	public static final String STORAGE_UPDATE_BY_ID = "UPDATE TB_STORAGE SET NUM=? WHERE ID=?";
	public static final String STORAGE_UPDATERATIO_BY_ID = "UPDATE TB_STORAGEWARN SET MAXNUM=?,MINNUM=?,USERID=?,MODETIME=SYSDATE WHERE ID=?";
	public static final String STORAGE_DEL_BY_ID = "DELETE FROM TB_STORAGE WHERE ID=?";
	public static final String STORAGE_DELRATIO_BY_ID = "DELETE FROM TB_STORAGEWARN WHERE ID=?";
	public static final String STORAGE_INSERT_RATIO =" INSERT INTO TB_STORAGEWARN(ID,POINTID,MAXNUM,MINNUM,USERID,CREATETIME,MODETIME,CODE) VALUES (SEQ_STORAGEWARN.NEXTVAL,?,?,?,?,SYSDATE,SYSDATE,?)";			
	
	/**
	 * 考勤管理
	 */
	public static final String TIMERECORDER_INS = "INSERT INTO TB_TIMERECORDER(ID,USERID,WORKTIME,REACHTIME,CURRENTDATE,LONGITUDE,LATITUDE,STATE,ADDRESS,DOCID,UPLOADNAME,IMAGENAME,FILEPATH,POINTID) VALUES(SEQ_TIMERECORDER.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String TIMERECORDEROFF_INS = "INSERT INTO TB_SIGN_OFF(ID,USERID,WORKTIME,OFFTIME,CURRENTDATE,LONGITUDE,LATITUDE,STATE,ADDRESS,DOCID,UPLOADNAME,IMAGENAME,FILEPATH,POINTID) VALUES(SEQ_TIMERECORDER.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	
	/**
	 * 促销品编码
	 * */
	//插入促销品编码
	public static final String GIFTCODE_INSERT ="INSERT INTO TB_GIFTCODE (ID,GIFTCODE,GIFTNAME,ORGID,CREATETIME,ISVALID,MEMO) VALUES (SEQ_GIFTCODE.NEXTVAL,?,?,?,?,?,?)";
	//根据id删除促销品编码
	public static final String GIFTCODE_DELETE_BY_ID = "DELETE FROM TB_GIFTCODE WHERE ID = ? ";
	//根据id修改促销品编码
	public static final String GIFTCODE_UPDATE_BY_ID = "UPDATE TB_GIFTCODE SET GIFTNAME = ?,MEMO = ? WHERE ID = ? AND GIFTCODE = ?";
	//根据促销品编码查询促销品
	public static final String GIFTCODE_QUERY_BY_GIFTCODE = "SELECT COUNT(*) FROM TB_GIFTCODE WHERE GIFTCODE = ?";
	//根据促销品编码查询促销品
	public static final String GIFTCODE_QUERY_BY_GIFTNAME = "SELECT COUNT(*) FROM TB_GIFTCODE WHERE GIFTNAME = ?";
	
	/**
	 * 促销品
	 * */
	//插入促销品
	public static final String GIFT_INSERT ="INSERT INTO TB_GIFTS (ID,GIFTCODE,GIFTNUM,ORGID,ISVALID,STARTDATE,ENDDATE,CREATETIME,MEMO) VALUES (SEQ_GIFTS.NEXTVAL,?,?,?,?,?,?,?,?)";
	//根据id删除促销品
	public static final String GIFT_DELETE_BY_ID = "DELETE FROM TB_GIFTS WHERE ID = ? ";
	//根据id修改促销品
	public static final String GIFT_UPDATE_BY_ID = "UPDATE TB_GIFTS SET GIFTNUM = ?,ISVALID = ?,STARTDATE = ?,ENDDATE = ?,MEMO = ? WHERE ID = ?";
	
}
